Year to Date column on same card as monthly

Options
kboudrie
kboudrie Member
in Magic ETL
image.png

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

  • GrantSmith
    GrantSmith Coach
    Options

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Options

    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

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories