H-1B Analysis - Historical

Why?

After seeing a lot of news about H-1B, and its impacts. I wanted to do a data analysis based on the openly available data source from the US GOV site. Still I was not able to get the data which I expected to see, which actually would answer my questions such as:

• Impact of $100,000 H-1B Application Fee
• US Government Shutdown Impact
• Mass Layoffs and H-1B Hiring
• Investigation Into H-1B Abuse
• Geographic & Wage Shifts
  Currently I would like to proceed with the data that is available on the USCIS GOV site and later take it forward with the other questions as and when the data is procurable.

Data Source:

• https://www.uscis.gov/archive/h-1b-employer-data-hub-files
  • All the archives were downloaded (FY 2009-23).
  • Files downloaded on October 6th, 2025.

Cleaning & Data Preparation

• Downloaded all the Archive historic data hub file.
• Started using the already created database in SSMS - [H-1B Analysis].

CREATE DATABASE [H-1B Analysis];

• First created and loaded 2009 File into table [dbo].[H1B_DataHubExport] using the SQL Server Import and Export Wizard.
  • Parameters:
    • Data Source: Flat File Source,
    • Format: Delimited,
    • Text Qualifier: "
    • Header row delimiter: Comma
    • Header rows to skip: 0
    • Check: Column names in first data row
• Then the remaining 14 files, till 2023 were also loaded into the table H1B_DataHubExport for consolidated approach.
  • 2009 - 68919 rows transferred
  • 2010 - 55429 rows transferred
  • 2011 - 62874 rows transferred
  • 2012 - 56222 rows transferred
  • 2013 - 56079 rows transferred
  • 2014 - 56595 rows transferred
  • 2015 - 48544 rows transferred
  • 2016 - 53129 rows transferred
  • 2017 - 49786 rows transferred
  • 2018 - 55666 rows transferred
  • 2019 - 59441 rows transferred
  • 2020 - 55239 rows transferred
  • 2021 - 60806 rows transferred
  • 2022 - 59983 rows transferred
  • 2023 - 33332 rows transferred
    • 2020, 2021, 2022, 2023 files were imported separately due to some column length and characteristic issues. Imported by Flat File Import instead of the Import and Export Wizard.
• Also confirmed the above transferred records using the below code.

SELECT [Fiscal Year], COUNT(*) COUNTALL
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [Fiscal Year]
ORDER BY 1;

Additional Steps in Data Prep:

• Increasing the column size of the [Employer]

ALTER TABLE [dbo].[H1B_DataHubExport] 
ALTER COLUMN [Employer] NVARCHAR(255);

• It did not fix the problem, hence increased it to MAX.

ALTER TABLE [dbo].[H1B_DataHubExport] 
ALTER COLUMN [Employer] [varchar](max) NULL;

• Also updated the [Fiscal Year]

ALTER TABLE [dbo].[H1B_DataHubExport] 
ALTER COLUMN [Fiscal Year] [smallint] NULL;

• Converting varchar to float [Initial Approvals]

UPDATE [dbo].[H1B_DataHubExport]
SET [Initial Approvals] = REPLACE([Initial Approvals],',','');
---
ALTER TABLE [dbo].[H1B_DataHubExport] 
ALTER COLUMN [Initial Approvals] [float] NULL;

• Converting varchar to float [Initial Denials]

UPDATE [dbo].[H1B_DataHubExport]
SET [Initial Denials] = REPLACE([Initial Denials],',','');
---
ALTER TABLE [dbo].[H1B_DataHubExport] 
ALTER COLUMN [Initial Denials] [float] NULL;

• Converting varchar to float [Continuing Approvals]

UPDATE [dbo].[H1B_DataHubExport]
SET [Continuing Approvals] = REPLACE([Continuing Approvals],',','');
---
ALTER TABLE [dbo].[H1B_DataHubExport] 
ALTER COLUMN [Continuing Approvals] [float] NULL;

• Converting varchar to float [Continuing Denials]

UPDATE [dbo].[H1B_DataHubExport]
SET [Continuing Denials] = REPLACE([Continuing Denials],',','');
---
ALTER TABLE [dbo].[H1B_DataHubExport] 
ALTER COLUMN [Continuing Denials] [float] NULL;

• Converting varchar to tinyint [NAICS]

ALTER TABLE [dbo].[H1B_DataHubExport] 
ALTER COLUMN [NAICS] [tinyint] NULL;

• Converting varchar to nvarchar 50 [State]

ALTER TABLE [dbo].[H1B_DataHubExport] 
ALTER COLUMN [State] [nvarchar](50) NULL;

• Converting varchar to nvarchar 50 [City]

ALTER TABLE [dbo].[H1B_DataHubExport] 
ALTER COLUMN [City] [nvarchar](50) NULL;

• Converting varchar to nvarchar 50 [City]

ALTER TABLE [dbo].[H1B_DataHubExport] 
ALTER COLUMN [ZIP] [int] NULL;

• Converting varchar to nvarchar 50 [Tax_ID]

ALTER TABLE [dbo].[H1B_DataHubExport_Final]
ALTER COLUMN [Tax_ID] [smallint] NULL;

• To check which are causing the issue in varchar to float

SELECT DISTINCT TRY_CAST([Initial Denials] AS FLOAT) FROM [dbo].[H1B_DataHubExport];

SELECT DISTINCT CAST(ISNULL(NULLIF(TRIM([Initial Denials]), ''), '0') AS FLOAT) FROM [dbo].[H1B_DataHubExport];

SELECT *
FROM [dbo].[H1B_DataHubExport]
WHERE TRY_CAST([Continuing Approvals] AS FLOAT) IS NULL
      AND [Continuing Approvals] IS NOT NULL
      AND LTRIM(RTRIM([Continuing Approvals])) <> '';

• Checking if all the data are inline for next steps

SELECT * FROM [dbo].[H1B_DataHubExport]
UNION ALL
SELECT * FROM [dbo].[h1b_datahubexport-2020]
UNION ALL
SELECT * FROM [dbo].[h1b_datahubexport-2021]
UNION ALL
SELECT * FROM [dbo].[h1b_datahubexport-2022]
UNION ALL
SELECT * FROM [dbo].[h1b_datahubexport-2023]; -- 832044

• Combing all the data into a single table

CREATE TABLE [dbo].[H1B_DataHubExport_Final](
	[Fiscal Year] [smallint] NULL,
	[Employer] [varchar](max) NULL,
	[Initial Approvals] [float] NULL,
	[Initial Denials] [float] NULL,
	[Continuing Approvals] [float] NULL,
	[Continuing Denials] [float] NULL,
	[NAICS] [tinyint] NULL,
	[Tax ID] [varchar](50) NULL,
	[State] [nvarchar](50) NULL,
	[City] [nvarchar](50) NULL,
	[ZIP] [int] NULL
);

INSERT INTO [dbo].[H1B_DataHubExport_Final]
SELECT * FROM [dbo].[H1B_DataHubExport]
UNION ALL
SELECT * FROM [dbo].[h1b_datahubexport-2020]
UNION ALL
SELECT * FROM [dbo].[h1b_datahubexport-2021]
UNION ALL
SELECT * FROM [dbo].[h1b_datahubexport-2022]
UNION ALL
SELECT * FROM [dbo].[h1b_datahubexport-2023]; 
-- (832044 rows affected)

• Checking if all the data is correctly loaded using count validation.

SELECT [Fiscal Year], COUNT(*) COUNTALL
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [Fiscal Year]
ORDER BY 1;

Exploring the Data

[Employer]

• Upon taking a count of [Employer] found only, 3,46,135 records.

SELECT DISTINCT [Employer]
FROM [dbo].[H1B_DataHubExport_Final];
---
SELECT COUNT(DISTINCT[Employer]) AS [Employer Count]
FROM [dbo].[H1B_DataHubExport_Final]; -- 346135

• Wanted to sort the count in a descending order.

SELECT [Employer], COUNT(*) AS [Employer Count]
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [Employer]
ORDER BY 2 DESC;

• Also wanted to check the top 10 [Employer]

SELECT TOP 10
[Employer], COUNT(*) AS [Count]
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [Employer]
ORDER BY 2 DESC;

[NAICS]

• Upon taking a count of [NAICS] found only, 25 records.

SELECT DISTINCT [NAICS]
FROM [dbo].[H1B_DataHubExport_Final];
---
SELECT COUNT(DISTINCT[NAICS]) AS [NAICS Count]
FROM [dbo].[H1B_DataHubExport_Final]; -- 25

• Wanted to sort the count in a descending order.

SELECT [NAICS], COUNT(*) AS [NAICS Count]
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [NAICS]
ORDER BY 2 DESC;

• Also wanted to check the top 5 [NAICS]

SELECT TOP 5
[NAICS], COUNT(*) AS [Count]
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [NAICS]
ORDER BY 2 DESC;

[City], [State], [Zip]

• Upon taking a count of [City], [State], [Zip] found only, 24301 records.

SELECT DISTINCT [City], [State], [ZIP]
FROM [dbo].[H1B_DataHubExport_Final]
ORDER BY 2,1; -- 24301
---
SELECT COUNT(*) AS COUNT FROM (
SELECT DISTINCT [City], [State], [ZIP]
FROM [dbo].[H1B_DataHubExport_Final]
) A; -- 24301

[City], [State]

• Upon taking a count of [City], [State] found only, 12841 records.

SELECT DISTINCT [City], [State]
FROM [dbo].[H1B_DataHubExport_Final]
ORDER BY 2,1; -- 12841
---
SELECT COUNT(*) AS COUNT FROM (
SELECT DISTINCT [City], [State]
FROM [dbo].[H1B_DataHubExport_Final]
) A; -- 12841

• Wanted to sort the count in a descending order.

SELECT [City], [State], COUNT(*) AS [Count]
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [City], [State]
ORDER BY 3 DESC;

• Also wanted to check the top 10 [City], [State]

SELECT TOP 10
[City], [State], COUNT(*) AS [Count]
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [City], [State]
ORDER BY 3 DESC;

[State]

• Upon taking a count of [State] found only, 62 records.

SELECT DISTINCT [State]
FROM [dbo].[H1B_DataHubExport_Final]
ORDER BY 1; -- 62
---
SELECT COUNT(*) AS COUNT FROM (
SELECT DISTINCT [State]
FROM [dbo].[H1B_DataHubExport_Final]
) A; -- 62

• Wanted to sort the count in a descending order.

SELECT [State], COUNT(*) AS [Count]
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [State]
ORDER BY 2 DESC;

• Also wanted to check the top 10 [State]

SELECT TOP 10
[Petitioner State], COUNT(*) AS [Count]
FROM [dbo].['Employer Information']
GROUP BY [Petitioner State]
ORDER BY 2 DESC;

Remaining Columns

SELECT SUM([Initial Approvals]) AS [Total Initial Approvals]
FROM [dbo].[H1B_DataHubExport_Final]; -- 1411508 > 1675596

SELECT SUM([Initial Denials]) AS [Total Initial Denials]
FROM [dbo].[H1B_DataHubExport_Final]; -- 164511 > 188454

SELECT SUM([Continuing Approvals]) AS [Total Continuing Approvals]
FROM [dbo].[H1B_DataHubExport_Final]; -- 2563437 > 3201196

SELECT SUM([Continuing Denials]) AS [Total Continuing Denials]
FROM [dbo].[H1B_DataHubExport_Final]; -- 142306 > 171745

Performing Data Analysis

1. Total Initial Approvals Each Year

SELECT 
[Fiscal Year], 
SUM([Initial Approvals]) AS Total_Initial_Approvals
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [Fiscal Year]
ORDER BY [Fiscal Year];

Shows the total number of initial H-1B approvals for each fiscal year.

2. Top 10 Employers by Initial Approvals (All Years)

SELECT TOP 10 
[Employer], 
SUM([Initial Approvals]) AS Total_Initial_Approvals
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [Employer]
ORDER BY Total_Initial_Approvals DESC;

Finds companies with the highest initial H-1B approvals overall.

3. Year-over-Year Growth/Decline in Initial Approvals

SELECT a.[Fiscal Year], a.Total_Initial_Approvals, 
  a.Total_Initial_Approvals - b.Total_Initial_Approvals AS YoY_Change
FROM (
  SELECT [Fiscal Year], SUM([Initial Approvals]) AS Total_Initial_Approvals
  FROM [dbo].[H1B_DataHubExport_Final]
  GROUP BY [Fiscal Year]
) a
LEFT JOIN (
  SELECT [Fiscal Year], SUM([Initial Approvals]) AS Total_Initial_Approvals
  FROM [dbo].[H1B_DataHubExport_Final]
  GROUP BY [Fiscal Year]
) b
ON a.[Fiscal Year] = b.[Fiscal Year] + 1
ORDER BY a.[Fiscal Year];

Shows the year-over-year change in initial approvals.

4. Denial Rates by NAICS Industry

SELECT [NAICS], 
  SUM([Initial Denials]) AS Total_Initial_Denials,
  SUM([Initial Approvals]) AS Total_Initial_Approvals,
  (CAST(SUM([Initial Denials]) AS FLOAT) / NULLIF(SUM([Initial Approvals]) + SUM([Initial Denials]), 0)) AS Initial_Denial_Rate
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [NAICS]
ORDER BY Initial_Denial_Rate DESC;

Calculates initial denial rates by industry.

5. States with Highest H-1B Activity

SELECT TOP 10
	[State], 
	SUM([Initial Approvals] + [Initial Denials] + [Continuing Approvals] + [Continuing Denials]) AS Total_Activity
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [State]
ORDER BY Total_Activity DESC;

Ranks states by total H-1B petition activity. (Top 10)

6. Top Cities for H-1B Approvals

SELECT TOP 10
	[City], 
	SUM([Initial Approvals] + [Continuing Approvals]) AS Total_Approvals
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [City]
ORDER BY Total_Approvals DESC;

Shows cities with the most approvals across all years.

7. Employer with Highest Denial Rate

SELECT TOP 10
  [Employer],
  SUM([Initial Denials]) AS Total_Denials,
  SUM([Initial Approvals]) AS Total_Approvals,
  (CAST(SUM([Initial Denials]) AS FLOAT) / NULLIF(SUM([Initial Denials]) + SUM([Initial Approvals]), 0)) AS Denial_Rate
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [Employer]
HAVING SUM([Initial Denials]) + SUM([Initial Approvals]) > 50
ORDER BY Denial_Rate DESC;

Identifies employers with high initial denial rates among frequent filers.

8. Yearly Trends for Continuing Approvals

SELECT [Fiscal Year], SUM([Continuing Approvals]) AS Total_Continuing_Approvals
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [Fiscal Year]
ORDER BY [Fiscal Year];

Tracks continuing approvals over time.

9. Employers Operating in Multiple States

SELECT TOP 10
[Employer], 
COUNT(DISTINCT [State]) AS State_Count
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [Employer]
HAVING COUNT(DISTINCT [State]) > 1
ORDER BY State_Count DESC;

Finds employers with offices across several U.S. states.

10. ZIP Codes with Most H-1B Activity

SELECT TOP 10
[ZIP], 
SUM([Initial Approvals] + [Continuing Approvals]) AS Total_Approvals
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [ZIP]
ORDER BY Total_Approvals DESC;

Highlights ZIP codes with high approval counts.

11. Average Approvals Per Employer

SELECT AVG(Total_Approvals) AS Avg_Approvals_Per_Employer
FROM (
  SELECT [Employer], SUM([Initial Approvals] + [Continuing Approvals]) AS Total_Approvals
  FROM [dbo].[H1B_DataHubExport_Final]
  GROUP BY [Employer]
) a;

Calculates the average number of approvals per employer across dataset.

14.0892366006425

12. NAICS Sectors with Highest H-1B Approvals

SELECT TOP 10 
[NAICS], 
SUM([Initial Approvals] + [Continuing Approvals]) AS Total_Approvals
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [NAICS]
ORDER BY Total_Approvals DESC;

Shows industry sectors (by NAICS) most likely to employ H-1B workers.

13. Initial vs. Continuing Approval Trend Over Years

SELECT [Fiscal Year], 
    SUM([Initial Approvals]) AS Initial_Approvals,
    SUM([Continuing Approvals]) AS Continuing_Approvals
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [Fiscal Year]
ORDER BY [Fiscal Year];

Compares yearly trends between new and continuing approvals.

14. Top 10 Employers in Most Recent Year

SELECT TOP 10 
[Employer], 
SUM([Initial Approvals] + [Continuing Approvals]) AS Total_Approvals
FROM [dbo].[H1B_DataHubExport_Final]
WHERE 
[Fiscal Year] = 
(SELECT MAX([Fiscal Year]) FROM [dbo].[H1B_DataHubExport_Final])
GROUP BY [Employer]
ORDER BY Total_Approvals DESC;

Finds leading H-1B employers for the latest year available.

15. Denial Rate Trend Over Years

SELECT [Fiscal Year], 
  SUM([Initial Denials]) AS Denials, 
  SUM([Initial Approvals]) AS Approvals,
  (CAST(SUM([Initial Denials]) AS FLOAT) / NULLIF(SUM([Initial Denials]) + SUM([Initial Approvals]),0)) AS Annual_Denial_Rate
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [Fiscal Year]
ORDER BY [Fiscal Year];

Shows how the initial denial rate changes over time.

16. Employers Filing in Only One NAICS Sector

SELECT [Employer], COUNT(DISTINCT [NAICS]) AS NAICS_Count
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [Employer]
HAVING COUNT(DISTINCT [NAICS]) = 1
ORDER BY [Employer]

Lists companies that are only affiliated with a single NAICS/industry.

3,20,989 rows

Advance Data Analysis

1. Moving Average of Initial Approvals Over Years

SELECT 
  [Fiscal Year],
  SUM([Initial Approvals]) AS Approvals,
  AVG(SUM([Initial Approvals])) OVER (
    ORDER BY [Fiscal Year] 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS Moving_Avg_Initial_Approvals
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [Fiscal Year]
ORDER BY [Fiscal Year]

Calculates a 3-year moving average of initial approvals, smoothing year-to-year volatility.

2. Top Employer Each Year (Correlated Subquery)

SELECT t.[Fiscal Year], t.[Employer], t.Total_Approvals
FROM (
  SELECT 
    [Fiscal Year],
    [Employer],
    SUM([Initial Approvals] + [Continuing Approvals]) AS Total_Approvals
  FROM [dbo].[H1B_DataHubExport_Final]
  GROUP BY [Fiscal Year], [Employer]
) t
WHERE t.Total_Approvals = (
    SELECT TOP 1 SUM([Initial Approvals] + [Continuing Approvals])
    FROM [dbo].[H1B_DataHubExport_Final] t2
    WHERE t2.[Fiscal Year] = t.[Fiscal Year]
    GROUP BY t2.[Employer]
    ORDER BY SUM([Initial Approvals] + [Continuing Approvals]) DESC
)
ORDER BY t.[Fiscal Year];

Finds the single employer with the highest total approvals for every fiscal year.

3. Year-on-Year % Growth of Initial Approvals

WITH cte AS (
  SELECT 
    [Fiscal Year], 
    SUM([Initial Approvals]) AS Total_Initial_Approvals
  FROM [dbo].[H1B_DataHubExport_Final]
  GROUP BY [Fiscal Year]
)
SELECT 
  [Fiscal Year],
  Total_Initial_Approvals,
  LAG(Total_Initial_Approvals, 1) OVER (ORDER BY [Fiscal Year]) AS Prev_Year_Approvals,
  CASE 
    WHEN LAG(Total_Initial_Approvals, 1) OVER (ORDER BY [Fiscal Year]) > 0 THEN
      ROUND(
        100.0 * (Total_Initial_Approvals - LAG(Total_Initial_Approvals, 1) OVER (ORDER BY [Fiscal Year]))
        / LAG(Total_Initial_Approvals, 1) OVER (ORDER BY [Fiscal Year]),2)
    ELSE NULL END AS YoY_Percent_Change
FROM cte
ORDER BY [Fiscal Year];

Shows the annual percentage change in initial approvals.

4. Share of Initial Denials by State (Window Function Partitioned by Year)

SELECT 
  [Fiscal Year], [State],
  SUM([Initial Denials]) AS Denials,
  SUM([Initial Denials]) * 1.0 / SUM(SUM([Initial Denials])) OVER (PARTITION BY [Fiscal Year]) AS State_Denial_Share
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY [Fiscal Year], [State]
ORDER BY [Fiscal Year], State_Denial_Share DESC

Reveals what fraction of initial denials are contributed by each state for every year.

852 rows

5. Employers with an Increasing Trend in Approvals Over 3 Years

WITH EmployerYear AS (
  SELECT
    [Employer],
    [Fiscal Year],
    SUM([Initial Approvals] + [Continuing Approvals]) AS Total_Approvals
  FROM [dbo].[H1B_DataHubExport_Final]
  GROUP BY [Employer], [Fiscal Year]
)
SELECT e1.[Employer], e1.[Fiscal Year], e1.Total_Approvals
FROM EmployerYear e1
JOIN EmployerYear e2 ON e1.[Employer] = e2.[Employer] AND e1.[Fiscal Year] = e2.[Fiscal Year] + 1
JOIN EmployerYear e3 ON e1.[Employer] = e3.[Employer] AND e1.[Fiscal Year] = e3.[Fiscal Year] + 2
WHERE e1.Total_Approvals > e2.Total_Approvals 
  AND e2.Total_Approvals > e3.Total_Approvals
ORDER BY e1.[Employer], e1.[Fiscal Year]

Finds employers whose approval counts have increased for three consecutive years.

18,773 rows - Employers

Data Visualization

• Currently, there are round 832044 row in the consolidated file. It will be difficult to handle such a big file.

SELECT COUNT(*) COUTNALL FROM [dbo].[H1B_DataHubExport_Final];

• Lets do an aggregation to reduce the count.
  • We can remove ZIP to reduce the count much further.

SELECT 
[Fiscal Year],
[Employer],
SUM([Initial Approvals]),
SUM([Initial Denials]),
SUM([Continuing Approvals]),
SUM([Continuing Denials]),
[NAICS],
[Tax ID],
[State],
[City],
[ZIP]
FROM [dbo].[H1B_DataHubExport_Final]
GROUP BY
[Fiscal Year],
[Employer],
[NAICS],
[Tax ID],
[State],
[City],
[ZIP];

• Counts

  • Without Aggregations: 8,32,044
  • Aggregations With ZIP: 8,18,210
  • Aggregation Without ZIP: 8,05,401 ✅
    • Proceeding with this result set.

• Extracting the data into CSV format to be used in Tableau.

• Visualization using Tableau Desktop Public Edition 2025.02.

• Preparation Work:

  • Imported the Excel data file into Tableau.
  • Created Calculated Fields for SUM of all Approvals, Denials.
  • Updated > Maps > Edit Locations. (Country: United States; State: Petitioner State Columns)
    • This will align and auto map, using Matching Location.

• Activity:

  • Added State to Marks - Details.
  • Added SUM(Approval)+(Denial) to Marks - Color, for Activity.
  • Added Longitude and Latitude (generated) to Columns and Rows respectively.
  • Added all the SUM(Approval) and SUM(Denial) to the Marks - Text, in the same order present in the Source.

• Approval:

  • Added SUM(Approval) to the Marks - Text.
  • Added Employer to the rows.
  • Did a filter by SUM(Approval), to reduce the number of records.

• Denial:

  • Added SUM(Denial) to the Marks - Text.
  • Added Employer to the rows.
  • Did a filter by SUM(Denial), to reduce the number of records.

• List:

  • Added all the Measures using this Mark - Text. Formatting:

Continuing Approvals: <SUM(Continuing Approvals)>
Continuing Denials: <SUM(Continuing Denials)>
Initial Approvals: <SUM(Initial Approvals)>
Initial Denials: <SUM(Initial Denials)>

• NAICS
  • Added all the Consolidated Measures using this Mark - Text. Formatting:

Approval: <SUM(Approval)>
Denials: <SUM(Denials)>

• Year Over Year:
  • Created a Calculated filed called YOY and Analysis Year
  • Added all the Measures to the rows.
  • Added Analysis Year to the columns.
  • Changed the color schema appropriately.

// YOY
CASE [Fiscal Year] - {MAX([Fiscal Year])}
    WHEN 0 THEN 'TY'
    WHEN -1 THEN 'LY'
    WHEN -2 THEN 'PY'
    WHEN -3 THEN '3Y'
    WHEN -4 THEN '4Y'
    WHEN -5 THEN '5Y'
    WHEN -6 THEN '6Y'
    WHEN -7 THEN '7Y'
    WHEN -8 THEN '8Y'
    WHEN -9 THEN '9Y'
    WHEN -10 THEN '10Y'
    WHEN -11 THEN '11Y'
    WHEN -12 THEN '12Y'
    WHEN -13 THEN '13Y'
    WHEN -14 THEN '14Y'
    WHEN -15 THEN '15Y'
END

// Analysis Year
IF [Fiscal Year] = {MAX([Fiscal Year])} 
THEN 'TY (' + STR([Fiscal Year]) + ')'
ELSEIF [Fiscal Year] = {MAX([Fiscal Year])} - 1 
THEN 'LY (' + STR([Fiscal Year]) + ')'
ELSEIF [Fiscal Year] < {MAX([Fiscal Year])} 
THEN STR(ABS([Fiscal Year] - {MAX([Fiscal Year])})) + 'Y (' + STR([Fiscal Year]) + ')'
ELSE '+' + STR([Fiscal Year] - {MAX([Fiscal Year])}) + 'Y (' + STR([Fiscal Year]) + ')'
END

• Approvals & Denials Trend:
  • Organized the Worksheets into this dashboard.
  • Added my custom logo, links to LinkedIn, Digital Garden, Online Resume.

Tableau Visualization - Link