Index Data Calculation

user01052

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!

Unknown

It looks like a simple syntax issue:

 

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

GrantSmith

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.

jaeW_at_Onyx

---

@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