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

bstephan
bstephan Member
in Beast Mode

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.

Screenshot 2023-06-14 at 3.34.38 PM.png

Tagged:

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    edited June 2023 Answer ✓

    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!

    If I solved your problem, please select "yes" above

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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
    MichelleH Coach
    Answer ✓

    @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`.

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited June 2023 Answer ✓

    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!

    If I solved your problem, please select "yes" above

  • MichelleH
    MichelleH Coach
    Answer ✓

    @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
    bstephan Member

    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
    MichelleH Coach

    @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
    bstephan Member

    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
    MichelleH Coach
    Answer ✓

    @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`.

Categories