% Variance Between Columns

Not ideal, but my workaround is using Dynamic SQL with Freehand SQL reports to produce dynamic column headers. It works, but may be difficult for others to support.

DECLARE @ColumnTwo VARCHAR(10)

SELECT @ColumnTwo=FORMAT(CONVERT(DATE, DATEADD(WK, -2, GETDATE())),'M/d/y')

DECLARE @SQL NVARCHAR(MAX)

SET @SQL=

N'SELECT 1 AS Sort, R.current_route_name AS Route,

SUM(CASE WHEN D.calendar_date=''2020-03-04'' THEN F.adjusted_boardings ELSE 0 END) AS ''3/4/2020'',

SUM(CASE WHEN D.calendar_date='+''''+@ColumnTwo+''''+' THEN F.adjusted_boardings ELSE 0 END) AS '+''''+@ColumnTwo+''''+',

(SUM(CASE WHEN D.calendar_date='+''''+@ColumnTwo+''''+' THEN F.adjusted_boardings ELSE 0 END)-

SUM(CASE WHEN D.calendar_date=''2020-03-04'' THEN F.adjusted_boardings ELSE 0 END)

)/NULLIF(SUM(CASE WHEN D.calendar_date=''2020-03-04'' THEN F.adjusted_boardings ELSE 0 END),0) AS PercentChange

FROM fact.fact_factored_statistics F

JOIN dim.dim_route R ON R.dim_route_key=F.dim_route_key

JOIN dim.dim_date D ON D.dim_date_key=F.dim_date_key

WHERE D.calendar_date IN (''2020-03-04'', '+''''+@ColumnTwo+''''+')

GROUP BY R.current_route_name

UNION ALL

SELECT 2 AS Sort, ''Total'',

SUM(CASE WHEN D.calendar_date=''2020-03-04'' THEN F.adjusted_boardings ELSE 0 END) AS ''3/4/2020'',

SUM(CASE WHEN D.calendar_date='+''''+@ColumnTwo+''''+' THEN F.adjusted_boardings ELSE 0 END) AS '+''''+@ColumnTwo+''''+',

(SUM(CASE WHEN D.calendar_date='+''''+@ColumnTwo+''''+' THEN F.adjusted_boardings ELSE 0 END)-

SUM(CASE WHEN D.calendar_date=''2020-03-04'' THEN F.adjusted_boardings ELSE 0 END)

)/NULLIF(SUM(CASE WHEN D.calendar_date=''2020-03-04'' THEN F.adjusted_boardings ELSE 0 END),0) AS PercentChange

FROM fact.fact_factored_statistics F

JOIN dim.dim_route R ON R.dim_route_key=F.dim_route_key

JOIN dim.dim_date D ON D.dim_date_key=F.dim_date_key

WHERE D.calendar_date IN (''2020-03-04'', '+''''+@ColumnTwo+''''+')'

EXEC (@SQL)

Here's how it comes out...I understand this would be hard to implement, but would be nice to be able to have this be a standard feature. I'm wondering if maybe a custom function with percent change, group by and parameter values to produce the column names would be possible.

Hi Craig,

Thanks for reaching out.

Yes, we can use the Append Sub Query along with the Calculated field as the 3rd column to get the desired output. Once we have both ColA and ColB from append sub queries, we can then add a 'calculated field' in the master query.

Please note that the fields from both master query and sub query can be only be accessed if we are creating the calculated field in master query.

I have attached a screenshot for your reference. In the example, 2012 and 2014 are the fields from append sub queries which have metric values in the respective years. And calculated field is created in the master query tab with the desired formula.

I hope that helps. Please let me know if you have any issues.

Best Regards,

Deepak

Very cool! So to be clear, are you doing method 1 or method 2 below:

Method 1:

1. Create master query with athlete region and sum invoiced amount
2. Create subquery 1 with 2012 aggregate
3. Create subquery 2 with 2014 aggregate
4. On master query, add calculated field using append 1 & 2

Method 2:

1. Create master query with athlete region and sum invoiced amount and sum of 2012
2. Create subquery with sum of 2014
3. Add calc field in master query

Hi Craig,

I have followed the method 1, which is to create a master query with athlete region and sum invoiced amount, and then appended two sub queries for the years 2012 and 2014. Finally, add a calculated field in the master query to calc the %variance.

Method 1 would be the right approach as it will ensure that we won't lose any rows from the athlete region column(dimension) even if there aren't any corresponding values in the sum invoiced(metric) for the year 2012. In the example, I have added an additional sum invoiced column in the master query just to understand the difference between the filtered values from the sub queries(2012 &2014) and total. Apologies if that have created a confusion.

Please let me know if that clarifies your question.

Best Regards,

Deepak

Figured as much but wanted to check. Thanks! I was thinking I was going to have to use Dynamic SQL for anything like this. Definitely works but difficult to maintain and support.

Thanks, I'll check it out!

Hi Craig,

Yes, that's true. It's much easier in YF.

And I am glad that I was able to help you.

I will mark this as resolved. Please feel free to reach out if there is anything that I can help you with.

Warm Regards,Deepak

Deepak,

Worked perfectly, thanks! I wish you could set up dynamic column-headers like with dynamic SQL but it's just a minor issue. It just means that I have to periodically remember to change the header names. But all the calculations work perfectly. Thanks for the suggestion! I posted a draft example.

Hi Craig,

I am so glad to hear that it worked.

Thank you very much for sharing the idea of a dynamic column header name that changes according to the data(year selected). We already have an idea logged for the same in our system which is under review by the dev team. I will provide you with an update once I hear back from the team.

Please keep sharing your ideas/views as we love to hear about new ways we can make the product even better and easier to use.

Best Regards,

Deepak