Year to Date column on same card as monthly

kboudrie

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?

GrantSmith

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.

jaeW_at_Onyx

just stack your data with a date dimension.

https://www.youtube.com/watch?v=CDKNOmKClms&t=628s

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