I need to convert the following beast mode to Group by

Options
Absher_645
Absher_645 Member
edited July 7 in Calculations & Variables (Beast Mode)

SUM(served) OVER (PARTITION BY Physical County,CABINET) / Sum(Passed) OVER (PARTITION BY Physical County,CABINET )*100

Answers

  • Absher_645
    Absher_645 Member

    This calculation uses both aggregated and non-aggregated columns. This may result in duplicate rows and unexpected behavior in charts. See this article for more information.

  • Absher_645
    Absher_645 Member

    I am getting duplicates and unexpected behavior

  • ArborRose
    ArborRose Coach

    Can you just put Physical County and Cabinet on a card and then add a formula (beast mode) as 

    (SUM(`served`) / SUM(`passed`)) * 100

    Your group by is in essence the Physical County, Cabinet.

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

  • Absher_645
    Absher_645 Member

    Tried that and the SUM was wrong before the math was good just inconsistent and duplicating

  • ArborRose
    ArborRose Coach

    What about using FIXED? Something like

    SUM(`served`) FIXED (BY `Physical County`, `CABINET`) / 
    
NULLIF(SUM(`passed`) FIXED (BY `Physical County`, `CABINET`), 0) * 100

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

  • Absher_645
    Absher_645 Member

    that kinda worked but now I get

    An issue has occurred during processing. We are unable to complete the request at this time - 'undefined'

    and am unable to save the card

  • ArborRose
    ArborRose Coach
    edited June 13

    A bit difficult to visualize without an anonymized data example. I'm thinking there's an issue with NULL.

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

Categories