How do I Compare Column A vs Column B in the same Value?

bstephan

I have one value that goes back several years and id like to compare TY vs PY. How do i write the calculation in beast mode to subtract A vs B and finally show the % ∆? Thank you.

ColemenWilson

One way you could do it is the beastmode below:

SUM(CASE WHEN `YEAR` = YEAR(CURRENT_DATE()) THEN `Sessions` ELSE 0 END) - SUM(CASE WHEN `YEAR` = YEAR(CURRENT_DATE()) - INTERVAL 1 YEAR THEN `Sessions` ELSE 0 END)

If this doesn't work let me know!

MichelleH

@bstephan I agree with @colemenwilson about using a beast mode column to show the % difference. Mega Table cards are actually better suited than Pivot Tables for showing variances, so you will need to use beast modes to re-create your TY and PY columns and add the variances.

Current Year:

SUM(case when `Year` = YEAR(CURRENT_DATE()) then `Sessions` else 0 end)

Prior Year:

SUM(case when `Year` = YEAR(DATE_SUB(CURRENT_DATE(),interval 1 year)) then `Sessions` else 0 end)

Variance:

SUM(case when `Year` = YEAR(CURRENT_DATE()) then `Sessions` else 0 end) 
- 
SUM(case when `Year` = YEAR(DATE_SUB(CURRENT_DATE(),interval 1 year)) then `Sessions` else 0 end)

% Variance:

(SUM(case when `Year` = YEAR(CURRENT_DATE()) then `Sessions` else 0 end) 
- 
SUM(case when `Year` = YEAR(DATE_SUB(CURRENT_DATE(),interval 1 year)) then `Sessions` else 0 end))
/
SUM(case when `Year` = YEAR(DATE_SUB(CURRENT_DATE(),interval 1 year)) then `Sessions` else 0 end)

MichelleH

@bstephan Yes, that's correct. I'd suggest creating a branch of your ETL that uses a formula tile to move the dates forward by 1 day.

DATE_ADD(`DateField`,interval 1 year)

Then you can rename the `Sessions` field of this branch `PY Sessions`. From there, use an Append Rows tile to merge that with the original data. Once the data is merged, use a Group by tile to aggregate `Sessions` and `PY Sessions` by `DateField` and `Sessions default channel group`.

ColemenWilson

One way you could do it is the beastmode below:

SUM(CASE WHEN `YEAR` = YEAR(CURRENT_DATE()) THEN `Sessions` ELSE 0 END) - SUM(CASE WHEN `YEAR` = YEAR(CURRENT_DATE()) - INTERVAL 1 YEAR THEN `Sessions` ELSE 0 END)

If this doesn't work let me know!

MichelleH

@bstephan I agree with @colemenwilson about using a beast mode column to show the % difference. Mega Table cards are actually better suited than Pivot Tables for showing variances, so you will need to use beast modes to re-create your TY and PY columns and add the variances.

Current Year:

SUM(case when `Year` = YEAR(CURRENT_DATE()) then `Sessions` else 0 end)

Prior Year:

SUM(case when `Year` = YEAR(DATE_SUB(CURRENT_DATE(),interval 1 year)) then `Sessions` else 0 end)

Variance:

SUM(case when `Year` = YEAR(CURRENT_DATE()) then `Sessions` else 0 end) 
- 
SUM(case when `Year` = YEAR(DATE_SUB(CURRENT_DATE(),interval 1 year)) then `Sessions` else 0 end)

% Variance:

(SUM(case when `Year` = YEAR(CURRENT_DATE()) then `Sessions` else 0 end) 
- 
SUM(case when `Year` = YEAR(DATE_SUB(CURRENT_DATE(),interval 1 year)) then `Sessions` else 0 end))
/
SUM(case when `Year` = YEAR(DATE_SUB(CURRENT_DATE(),interval 1 year)) then `Sessions` else 0 end)

bstephan

Thank you, that worked well. If I may, is it possible to use the global date filters in the dashboard i.e. Month to date, or YTD and then have the table respond with the proper Var and % Var?

MichelleH

@bstephan With this setup, changing the global date filter to Month to Date would exclude all of last year's data. The best way to make the YoY variances to dynamically update with the global date filters is to restructure your data in a dataflow to add a separate dataset column for prior year sessions. Then you could replace the prior year calculations with the prior year dataset column in your cards.

bstephan

When I create a new dataset column for prior year sessions, would I need to move the data from prior year to land on same day this year? Moving the data forward 364 days, so when you do the variance it's accounting for TY vs LY in the table? If the answer is yes, how do you move forward sessions to fall on a new day?

MichelleH

@bstephan Yes, that's correct. I'd suggest creating a branch of your ETL that uses a formula tile to move the dates forward by 1 day.

DATE_ADD(`DateField`,interval 1 year)

Then you can rename the `Sessions` field of this branch `PY Sessions`. From there, use an Append Rows tile to merge that with the original data. Once the data is merged, use a Group by tile to aggregate `Sessions` and `PY Sessions` by `DateField` and `Sessions default channel group`.