Help with a complex calculation that isn't working in Beast Mode.

Trumpetbob

I am trying to calculate a comparison of an individual's performance compared to the established average. This is for setting appointments. The appointment conversion ratio is found by (appointments set / number of contacts). The appointment conversion ratio / average gets the performance multiplier. 1 being average, 2 being twice the average, etc.

Example:
CASE WHEN LeadLevel Name = 'B1'THEN
SUM(CASE WHEN Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.225 END

This is easy enough to calculate in a beast mode. However, there are 5 different lead types and each has a different average performance rate. I want to show the overall performance rate, and then be able to filter that with different dates, lead levels, etc. This is where I run into issues.

To calculate the overall performance rate, I take each lead level and calculate the performance rate. Then to get the proportion of the whole for that, I take the number of contacts at that lead level / number of total contacts and multiply the answer by the performance rate. I do this for each of the 5 lead levels and add the result together and that gives me the overall performance rate.

Example:
(SUM(CASE WHEN LeadLevel Name = 'B1' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END))
* CASE WHEN LeadLevel Name = 'B1'THENSUM(CASE WHEN Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.225 END)

The example is done for each lead level and then added together. But that doesn't work. It will work for one, but when I add multiple lead levels and try to add them together I get no result.

Not sure if I need a different approach. A thought is to calculate in an ETL, but I don't want to aggregate the data as I want to be able to change the date range and filter lead levels.

Any ideas?

GrantSmith

Likely what's happening is that one of your B2, B3 or B4 calculations is returning a NULL which will make your entire calculation NULL when using +, -, * or / operations thus not returning any data to your chart. You can try and wrap each in a COALESCE(formula, 0) call to make sure you're not adding NULLs together.

Alternatively based on your data you may be doing a divide by 0 as well you might need to handle with an outer CASE statement checking the denominator for 0 and then just returning 0 instead of attempting to divide by 0.

Typically in cases like this I'll break out the different sections of the beast mode (B2, B3 and B4 in your case) in to separate beast modes and then use a table chart to see how each section is functioning to help diagnose the errors.

MichelleH

@Trumpetbob Rather than adding a zero, I would follow @GrantSmith's suggestion and wrap each option in a COALESCE function to force null values to zero:

COALESCE( -- B2


(SUM(CASE WHEN LeadLevel Name = 'B2' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END))
* (SUM(CASE WHEN LeadLevel Name = 'B2' and Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN LeadLevel Name = 'B2' and Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.183)),0)


+


COALESCE( -- B3


(SUM(CASE WHEN LeadLevel Name = 'B3' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END))
* (SUM(CASE WHEN WHEN LeadLevel Name = 'B3' and Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN WHEN LeadLevel Name = 'B3' and Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.143)),0)


+


COALESCE( -- B4


(SUM(CASE WHEN LeadLevel Name = 'B4' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END))
* (SUM(CASE WHEN LeadLevel Name = 'B4' and Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN LeadLevel Name = 'B4' and Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.1)),0)

MichelleH

@Trumpetbob What do your calculation and result look like when you try to combine all 5 lead types? When you say it doesn't work, is it returning an error or an incorrect result?

Trumpetbob

@MichelleH It returns a blank or "No data in the filtered range."

Trumpetbob

Onboarding - Test Dials.txt

Here is my sample data.

With just one section of the Beast mode (B2) I get this:

And this is what I get with all three.

Here is my Beast Mode:

( -- B2
(SUM(CASE WHEN LeadLevel Name = 'B2' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END))
* CASE WHEN LeadLevel Name = 'B2'THENSUM(CASE WHEN Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.183 END)
+
( -- B3
(SUM(CASE WHEN LeadLevel Name = 'B3' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END))
* CASE WHEN LeadLevel Name = 'B3'THENSUM(CASE WHEN Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.143 END)
+
( -- B4
(SUM(CASE WHEN LeadLevel Name = 'B4' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END))
* CASE WHEN LeadLevel Name = 'B4'THENSUM(CASE WHEN Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.1 END)

GrantSmith

Likely what's happening is that one of your B2, B3 or B4 calculations is returning a NULL which will make your entire calculation NULL when using +, -, * or / operations thus not returning any data to your chart. You can try and wrap each in a COALESCE(formula, 0) call to make sure you're not adding NULLs together.

Alternatively based on your data you may be doing a divide by 0 as well you might need to handle with an outer CASE statement checking the denominator for 0 and then just returning 0 instead of attempting to divide by 0.

Typically in cases like this I'll break out the different sections of the beast mode (B2, B3 and B4 in your case) in to separate beast modes and then use a table chart to see how each section is functioning to help diagnose the errors.

MichelleH

@Trumpetbob I'd recommend modifying the second portion of each calculation to move the lead level criteria to within each case statement since the case statement not wrapped in a sum is likely causing granularity issues.

( -- B2


(SUM(CASE WHEN LeadLevel Name = 'B2' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END))
* (SUM(CASE WHEN LeadLevel Name = 'B2' and Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN LeadLevel Name = 'B2' and Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.183))


+


( -- B3


(SUM(CASE WHEN LeadLevel Name = 'B3' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END))
* (SUM(CASE WHEN WHEN LeadLevel Name = 'B3' and Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN WHEN LeadLevel Name = 'B3' and Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.143))


+


( -- B4


(SUM(CASE WHEN LeadLevel Name = 'B4' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END))
* (SUM(CASE WHEN LeadLevel Name = 'B4' and Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN LeadLevel Name = 'B4' and Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.1))

Trumpetbob

@ https://community-forums.domo.com/main/profile/MichelleH That makes sense. But when I do that I get no result again. If I limit to just one of the LeadLevel sections, it works for that one section. So something is still wrong.

Trumpetbob

I tried adding a +0 to the end of each statement, which works and shows a result. But I cannot filter by Lead level. Once I uncheck one it disappears again.

( -- B2
(SUM(CASE WHEN LeadLevel Name = 'B2' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END))

*(SUM(CASE WHEN LeadLevel Name = 'B2' and Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN LeadLevel Name = 'B2' and Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.183)+0)

+

( -- B3
(SUM(CASE WHEN LeadLevel Name = 'B3' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END))

*(SUM(CASE WHEN LeadLevel Name = 'B3' and Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN LeadLevel Name = 'B3' and Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.143)+0)

+

( -- B4
(SUM(CASE WHEN LeadLevel Name = 'B4' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END))

(SUM(CASE WHEN LeadLevel Name = 'B4' and Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN LeadLevel Name = 'B4' and Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.10)+0)

MichelleH

@Trumpetbob Rather than adding a zero, I would follow @GrantSmith's suggestion and wrap each option in a COALESCE function to force null values to zero:

COALESCE( -- B2


(SUM(CASE WHEN LeadLevel Name = 'B2' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END))
* (SUM(CASE WHEN LeadLevel Name = 'B2' and Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN LeadLevel Name = 'B2' and Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.183)),0)


+


COALESCE( -- B3


(SUM(CASE WHEN LeadLevel Name = 'B3' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END))
* (SUM(CASE WHEN WHEN LeadLevel Name = 'B3' and Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN WHEN LeadLevel Name = 'B3' and Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.143)),0)


+


COALESCE( -- B4


(SUM(CASE WHEN LeadLevel Name = 'B4' AND Contacted LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted LIKE '%Y%' THEN 1 ELSE 0 END))
* (SUM(CASE WHEN LeadLevel Name = 'B4' and Appointment Set LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN LeadLevel Name = 'B4' and Contacted LIKE '%Y%' THEN 1 ELSE 0 END) / 0.1)),0)

Trumpetbob

https://community-forums.domo.com/main/profile/MichelleH & https://community-forums.domo.com/main/profile/GrantSmith

Yes! That was it. Thank you https://community-forums.domo.com/main/profile/GrantSmith , that was exactly it.