Index Data Calculation

user01052
user01052 Member
in Magic ETL

Hi There,

 

I'd like to index data but my beast mode seems to be off. Below is what I'm trying to do but the calculation isn't working. 

Index Formula = 100*(Current Revenue/Revenue from Jan 1, 2020)

Beast Mode = 100 * (SUM(`revenue`) / SUM(CASE WHEN `day` = '01/01/2020' THEN sum(`revenue`) END))

 

Any help is appreciated!

Comments

  • Unknown
    Unknown

    It looks like a simple syntax issue:

     

    100 * (SUM(`revenue`) / SUM(CASE WHEN `day` = '01/01/2020' THEN sum(`revenue`) ELSE 0 END))


    ----------
    Serving up data insights since 2002...
  • GrantSmith
    GrantSmith Coach

    Hi @user01052 

     

    A couple issues with your beast mode. The first being you're comparing a date to a string which doesn't always work well. The second and most imporatantly is that your beast mode runs on a row by row basis so your denominator would always be null for dates after the first snice the dates aren't the first and you don't have an else clause (which @eric_etumos fixes with his contribution making it 0). But it still won't let you go back to the first of the year since it's a different row.

     

    However! To fix this we can use some windowing functions within a beast mode and coupling that with a case statement we can pull the data from the first of the year. I've tried to write this in such a way that it'll work going forward in the future (2021, 2022 etc) without having to edit the beast mode each year. Assuming your index date is always the first of the year.

     

    100 * SUM(`Revenue`) / SUM(SUM(CASE WHEN DAY(`Date`) = 1 and MONTH(`Date`) = 1 THEN `Revenue` ELSE 0 END)) OVER (PARTITION BY YEAR(`Date`))

     

    I'm not certain if windowing functions have made it out of beta yet at this point but you can reach out to your CSM to get it enabled in your instance.

    **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
    @eric_etumos wrote:

    It looks like a simple syntax issue:

     

    100 * (SUM(`revenue`) / SUM(CASE WHEN `day` = '01/01/2020' THEN sum(`revenue`) ELSE 0 END))

    EDIT:: i need to read everyone's responses before i start replying ... looks like @GrantSmith  beat me to it...

     

    The ELSE clause is a red herring.

    If you take the SUM of a column containing NULLS, SQL will gracefully handle that.  @eric_etumos , I believe you're thinking of what happens when SQL across two columns at the row-level

     

    ex

    SELECT col1 + col2_contains_NULL  ... which will result in that ROW returning NULL.

    whereas

    SELECT sum(col2_containsNULL) ... would return the sum of non NULL rows.

     

    either way, i don't believe that's the crux of the issue.

     

    @GrantSmith  sorted you out.

     

    This is just a permutation of a percent of total calc with a CASE statement.

    https://www.youtube.com/watch?v=ZPf41Fjn1H8&feature=youtu.be

     

    cc @swagner 

    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