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.5K Product Ideas
 1.5K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 284 Workbench
 4 Cloud Amplifier
 4 Federated
 2.7K Transform
 90 SQL DataFlows
 570 Datasets
 2K Magic ETL
 3.4K Visualize
 2.4K Charting
 605 Beast Mode
 18 App Studio
 29 Variables
 602 Automate
 152 Apps
 419 APIs & Domo Developer
 29 Workflows
 2 DomoAI
 28 Predict
 12 Jupyter Workspaces
 16 R & Python Tiles
 366 Distribute
 100 Domo Everywhere
 264 Scheduled Reports
 2 Software Integrations
 98 Manage
 95 Governance & Security
 15 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 90 Community Announcements
 4.8K Archive