Help with Beast Mode Formula

jimsteph · 2023-07-12T21:20:07+00:00

There was an error rendering this rich post.

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.

Find more posts tagged with

Beast Mode

Formulas

Accepted answers

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.

All comments

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.

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?!?).