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 select "yes" above
1 
@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 select "yes" above
1 
@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.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 292 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 95 SQL DataFlows
 603 Datasets
 2.1K Magic ETL
 3.7K Visualize
 2.4K Charting
 697 Beast Mode
 43 App Studio
 39 Variables
 658 Automate
 170 Apps
 441 APIs & Domo Developer
 42 Workflows
 5 DomoAI
 32 Predict
 12 Jupyter Workspaces
 20 R & Python Tiles
 388 Distribute
 111 Domo Everywhere
 271 Scheduled Reports
 6 Software Integrations
 113 Manage
 110 Governance & Security
 9 Domo University
 30 Product Releases
 Community Forums
 40 Getting Started
 30 Community Member Introductions
 98 Community Announcements
 Domo Community Gallery
 4.8K Archive