Help with Beast Mode Formula

jimsteph

I need help doing a complex sum in a beast mode, as when I do it I either get an error or a totally wrong number.

• The formula I was given reduces to A/(A+B-C), and sums across the entire data set for whatever date range the user chooses.
• Each of the letter values will be either a zero or one for a given record based on flags in that record: it's quite possible that an individual record will have no flags raised, making the denominator zero for that record.

When I sum up the values for the numerator and denominator separately and manually calculate the results I get the expected results (9.4%, in this case).

If I set my beast mode to SUM(A/(A+B-C)) I get a divide by zero error, as it calculates the formula per record before summing and one or more of the records will have the denominator calculate to zero.

If I do SUM(A) / SUM(A+B+C) or SUM(A) / (SUM(A) + SUM(B) - SUM(C)) I get 51%, which is totally wrong.

I'm obviously thinking about this wrong, so any perspective adjustments that make it work would be appreciated.

GrantSmith

@jimsteph

What happens if you use a table chart and then create separate beast modes for A, B and C to sum them all together and validate those sums are calculating correctly? Do you get the numbers you're expecting for each of those?

SUM(A) / (SUM(A) + SUM(B) - SUM(C)) should work according to your logic but without knowing the underlying logic being used in the beast mode to calculate the flags it's difficult to pinpoint the exact cause.

marcel_luthi

Just to be clear each record has a value for A, B and C, which will either be a 1 or 0 since they are basically flags and your card should show a single % based on all the entries that match your filter criteria (including date range).

Can you share the examples of the list of values for A, B and C you're using and how manually you're getting to 9.4%? This will allow us to understand the logic behind how it should be calculated via beast mode, or even better, lets suppose your filtered range would give you these 9 entries:

What would be the result you would expect and can you explain how you're getting to it?

jimsteph

This is correct. A, B, and C are derived with logic, but are still in the end flags.

Based on your dataset, I would expect to get 7/(7+4-5) = 7/6 = 116.67%

In the actual dataset there are 7.722 million records for the month to date. The sum of flag A for this period is 53,952, the sum of flag B is 547,753, and the sum of flag C is 29,834.

The sum of (A + B - C) is 571,871. Based on the formula A/(A+B-C) I should get a final answer of 9.434% (53,952 / 571,871), but when I use the formula SUM(A) / SUM(A + B - C) I get 51.516%; I get the same results when I do SUM(A) / (SUM(A) + SUM(B) - SUM(C)). I can't do SUM(A / (A + B - C))because there are records where none of the three flags are raised, so it says "You have a divide by zero error in your Card's Beast Mode".

I'm just not seeing what's going on, and it's annoying.

GrantSmith

@jimsteph

What happens if you use a table chart and then create separate beast modes for A, B and C to sum them all together and validate those sums are calculating correctly? Do you get the numbers you're expecting for each of those?

SUM(A) / (SUM(A) + SUM(B) - SUM(C)) should work according to your logic but without knowing the underlying logic being used in the beast mode to calculate the flags it's difficult to pinpoint the exact cause.

jimsteph

@GrantSmith

I followed your suggestion and converted to a table, used the ticket date for the first column and then put columns for A, B, C, (A+B+C), and the full formula, set all of those to SUM, and the calculations all worked. I was even able to get the summary number to be correct over the selected date range.

Here's a kick in the head: the Powers That Be want it to be a single number so I converted the card back and prepared to let you know it was still broken when, lo and behold, it worked! I saved it and slowly backed away, so now everyone should be happy (except for me: why did this work?!?).