What is the best way to calculate?
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
If this helps, feel free to agree, accept or awesome it!
Best Answer
-
@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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
1
Answers
-
@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).
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
1 -
@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.
If this helps, feel free to agree, accept or awesome it!
0 -
I even tried to do it without the instrument buckets beast modes taking place
still no values.
If this helps, feel free to agree, accept or awesome it!
0 -
@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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
1 -
That works. Thank you
If this helps, feel free to agree, accept or awesome it!
0 -
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.
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"1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 299 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 681 Automate
- 175 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 9 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 33 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive