Help with Beast Mode Formula

Options
jimsteph
jimsteph Contributor
in Beast Mode

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.

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓
    Options

    @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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • marcel_luthi
    marcel_luthi Coach
    edited July 2023
    Options

    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:

    image.png

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

  • jimsteph
    jimsteph Contributor
    edited July 2023
    Options

    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
    GrantSmith Coach
    Answer ✓
    Options

    @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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories