What is the best way to calculate?

damen

So I'm using a grouped bar graph and am looking to try and calculate the difference between two of my columns that were created.

My company calculates the average spread on loans broken down by lenders we use. I was able to separate those average spreads by product by adding the products to the 'series' drop-in at the top.

The next thing I'm trying to do is calculate the average spread on our 1st home product vs our home again product, but it still has to be broken down by lender.

(I did not include the lender names but each blue/green bar pairing represent an individual lender)

In essence, I am trying to create a formula where we subtract the blue columns from the green columns. TIA

RobSomers

@damen You'll need to remove 'instrument' from Series, and create the separate beast modes for each instrument you want to measure. Ex:

AVG(case when 'instrument'= 'first home exempt' then 'spread' end)

and a separate one:

AVG(case when 'instrument' = 'home again' then 'spread' end)

Then you'll take one of these and put in Value, and then put the other one in Series as well as the difference beast mode in Series You'll end up with something that looks like this:

It's not very intuitive and it looks weird, but it works.

RobSomers

@damen You should be able to do a beast mode like the following (using 'Product' for your 1st Home or Home Again column):

AVG(case when 'Product' = '1st Home' then 'spread' end) - AVG(case when 'Product' = 'Home Again' then 'spread' end)

If you want to keep your chart as is and have the difference as another bar, you'll need to do separate beast mode calculations for the spread of 1st Home and the spread of Home Again, then add those and the difference beast mode to the chart (same goes for doing a bar and line chart).

damen

@RobSomers For whatever reason, I'm not getting any calculations

AVG(case 

    when (CASE 

WHEN (`instrument` = 'first home') THEN '1st Home' 

 WHEN (`instrument` = 'fannie mae risk share') THEN 'NoMi'

 WHEN (`instrument` = 'fha streamline') THEN 'FSR' 

 WHEN (`instrument` = 'first home tax exempt') THEN '1st Home' 

 WHEN (`instrument` = 'freddie mac advantage ') THEN 'HFA Advantage' 

 WHEN (`instrument` = 'freddie mac risk share') THEN 'NoMi' 

 WHEN (`instrument` = 'freddie mac advantage') THEN 'HFA Advantage' 

 WHEN (`instrument` = 'fsr') THEN 'FSR' 

 WHEN (`instrument` = 'home again') THEN 'Home Again' 

 WHEN (`instrument` = 'NoMI') THEN 'NoMi'

 WHEN (`instrument` = 'score') THEN 'Score' 

 ELSE `instrument` 

 

END) = '1st Home' then `spread` end) 

 

 - AVG(case 

    when 

    

    (CASE 

 

 WHEN (`instrument` = 'first home') THEN '1st Home' 

 WHEN (`instrument` = 'fannie mae risk share') THEN 'NoMi'

 WHEN (`instrument` = 'fha streamline') THEN 'FSR' 

 WHEN (`instrument` = 'first home tax exempt') THEN '1st Home' 

 WHEN (`instrument` = 'freddie mac advantage ') THEN 'HFA Advantage' 

 WHEN (`instrument` = 'freddie mac risk share') THEN 'NoMi' 

 WHEN (`instrument` = 'freddie mac advantage') THEN 'HFA Advantage' 

 WHEN (`instrument` = 'fsr') THEN 'FSR' 

 WHEN (`instrument` = 'home again') THEN 'Home Again' 

 WHEN (`instrument` = 'NoMI') THEN 'NoMi'

 WHEN (`instrument` = 'score') THEN 'Score' 

 ELSE `instrument` 

 END) = 'Home Again' then `spread` end)

That is the 'asdf' beast mode

What I failed to mention was the fact that we had to create the 'product' bucket to begin with. That beast mode includes the syntax for that to happen.

The beast mode comes back valid but again, not sure why I wouldn't be getting any values.

damen

@RobSomers

I even tried to do it without the instrument buckets beast modes taking place

still no values.

RobSomers

@damen You'll need to remove 'instrument' from Series, and create the separate beast modes for each instrument you want to measure. Ex:

AVG(case when 'instrument'= 'first home exempt' then 'spread' end)

and a separate one:

AVG(case when 'instrument' = 'home again' then 'spread' end)

Then you'll take one of these and put in Value, and then put the other one in Series as well as the difference beast mode in Series You'll end up with something that looks like this:

It's not very intuitive and it looks weird, but it works.

damen

That works. Thank you

jaeW_at_Onyx

@damen

the learning lesson is you shouldn't have nested CASE statements

AVG(
case 
when (CASE
WHEN (`instrument` = 'first home') THEN '1st Home' 
WHEN (`instrument` = 'fannie mae risk share') THEN 'NoMi'
WHEN (`instrument` = 'fha streamline') THEN 'FSR' 
WHEN (`instrument` = 'first home tax exempt') THEN '1st Home' 
WHEN (`instrument` = 'freddie mac advantage ') THEN 'HFA Advantage' 
WHEN (`instrument` = 'freddie mac risk share') THEN 'NoMi' 
WHEN (`instrument` = 'freddie mac advantage') THEN 'HFA Advantage' 
WHEN (`instrument` = 'fsr') THEN 'FSR' 
WHEN (`instrument` = 'home again') THEN 'Home Again' 
WHEN (`instrument` = 'NoMI') THEN 'NoMi'
WHEN (`instrument` = 'score') THEN 'Score' 
ELSE `instrument` 
END) = '1st Home' then `spread` end) 

because if you boil it down.

your inner CASE statement will return a number.

so then in your outer CASE statement you have

CASE when <number> = '1st Home' then spread end.

well... it'll never = '1st Home'. so you always return NULL because you don't have an ELSE clause in place.