How do I Compare Column A vs Column B in the same Value?
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.
Best Answers

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 "accept" my answer as the solution
0 
@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 recreate 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)
0 
@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`.
0
Answers

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 "accept" my answer as the solution
0 
@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 recreate 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)
0 
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?
0 
@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.
1 
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?
0 
@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`.
0
Categories
 All Categories
 1.1K Product Ideas
 1.1K Ideas Exchange
 1.2K Connect
 970 Connectors
 259 Workbench
 Cloud Amplifier
 1 Federated
 2.4K Transform
 76 SQL DataFlows
 502 Datasets
 1.8K Magic ETL
 2.7K Visualize
 2.2K Charting
 379 Beast Mode
 21 Variables
 487 Automate
 104 Apps
 379 APIs & Domo Developer
 6 Workflows
 22 Predict
 6 Jupyter Workspaces
 16 R & Python Tiles
 319 Distribute
 67 Domo Everywhere
 252 Scheduled Reports
 59 Manage
 59 Governance & Security
 1 Product Release Questions
 5K Community Forums
 37 Getting Started
 23 Community Member Introductions
 64 Community Announcements
 4.8K Archive