Year to Date column on same card as monthly
I've been wrestling with this for several months and hope the Domo hive mind can help me out :)
My team uses several graphs like the above for our monthly metrics. "Monthly PPM" is a calculation of col_1
/col_2
*1000000. "YTD PPM" should be cumulative col_1
/ cumulative col_2
* 1000000 with the year starting fresh in May. So May Monthly PPM and YTD PPM are the same but June YTD PPM should be larger than the June Monthly PPM and should be (May col_1
+ June col_1
) / (May col_2
+ June col_2
) * 1000000. July YTD PPM will be (May col_1
+ June col_1
+ July col_1
) / (May col_2
+ June col_2
+ July col_2
) * 1000000 and so on and so forth.
Everything prior to May was driven by a Google sheet and the Domo card was just graphing it, no calculations. I'm trying to move to a more programmatic approach where the data loads into Domo and the calculations are done in Magic ETL to build a final dataset that looks similar to the old Google sheet. However, I'm stumped how to calculate the YTD!
Can anyone help me or am I attempting the impossible and should go back to the Google sheet method?
Answers

Hi @kboudrie
You can utilize case statements and window functions (you'll need to talk with your CSM to turn on windowing functions)
SUM(SUM(`col_1`)) OVER (PARTITION BY YEAR(`Date`  INTERVAL '5' MONTH) ORDER BY (`Date`  INTERVAL '5' MONTH) / SUM(SUM(`col_2`)) OVER (PARTITION BY YEAR(`Date`  INTERVAL '5' MONTH) ORDER BY (`Date`  INTERVAL '5' MONTH) * 1000000
It's subtracting 5 months since May is the start of your year.
A possibly better option is to have a Date dimension table which defines the fiscal year for each date instead of having the logic in a beast mode. You'd then join the date to your date dimension and partition by that field. This way all your date logic is on a single dataset instead of multiple beast modes.
1 
just stack your data with a date dimension.
the gist of it is. create a datastet for one row per date in YTD, and in clude a column "Period Type" = YTD. then UNION all thte dates for Period Type = MTD or and maybe another set of Dates where Period Type = Prev Year YTD
0
Categories
 10.6K All Categories
 APAC User Group
 12 Welcome
 36 Domo News
 9.6K Using Domo
 1.9K Dataflows
 2.4K Card Building
 2.2K Ideas Exchange
 1.2K Connectors
 337 Workbench
 250 Domo Best Practices
 11 Domo Certification
 460 Domo Developer
 47 Domo Everywhere
 100 Apps
 703 New to Domo
 83 Dojo
 Domopalooza
 1.1K 日本支部
 4 道場日本支部へようこそ
 22 お知らせ
 62 Kowaza
 295 仲間に相談
 649 ひらめき共有