Window Function on Fixed Column Finding Aggregations of Previous Beast Mode

random_user_098765
random_user_098765 Member
in Beast Mode

Hello,

I was hoping to find a way to take the MAX, MIN, and AVG of a calculation based on a column in a beast mode calculation. For example, I am creating the "Average Performer" as something like the below query but I am getting an error thrown.

AVG(AVG((SUM(COMPLETED) / SUM(EXPECTED))) FIXED (BY GROUP_1))

I was basing my work on this link: https://domo-support.domo.com/s/article/4408174643607?language=en_US . Does anyone have any suggestions on what I can do?

Tagged:

Best Answers

  • random_user_098765
    random_user_098765 Member
    edited November 2023 Answer ✓

    Hi, I found a column that represents this inner Completion Percentage beast mode but I am still having trouble with showing a line graph for the MAX, MIN, and AVG of the Completion Percentage column based on the GROUP_1 column. Can anyone help me out?

    Edit: This is not an answer so please disregard my misclick

  • random_user_098765
    random_user_098765 Member
    Answer ✓

    Hi, your answer was correct

Answers

  • DavidChurchman
    DavidChurchman Coach

    If you just want the average by group, you probably don't need a fixed function. You could just set the aggregation to average. In a table, for example, make the first column GROUP_1 and the second column your BeastMode.

    Or if that's not working, could you be more specific about what you mean by "average performer"?

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • random_user_098765
    random_user_098765 Member

    Hi, I am using these beast modes in the line graph card. I am not sure that the table solution will work. By 'Average Performer', it is someone who is the average performer based on the group for the rate of completed/expected

  • random_user_098765
    random_user_098765 Member

    To be more clear, I created a beast mode called 'Completion Percentage' with it being (SUM(COMPLETED) / SUM(EXPECTED). I am trying to find the MAX, MIN, AVG of this metric while fixing on the GROUP_1 column so that I can plot the Max Completion Percentage, the Min Completion Percentage and the Avg Completion Percentage over time based on the GROUP_1 column. I hope this helps :)

  • random_user_098765
    random_user_098765 Member
    edited November 2023 Answer ✓

    Hi, I found a column that represents this inner Completion Percentage beast mode but I am still having trouble with showing a line graph for the MAX, MIN, and AVG of the Completion Percentage column based on the GROUP_1 column. Can anyone help me out?

    Edit: This is not an answer so please disregard my misclick

  • DavidChurchman
    DavidChurchman Coach

    Since you're saying line graph, I'm assuming you want it not just fixed by category but also by date. If it's not fixed by date (or whatever your x variable is) it would be a bunch of horizontal lines. That would be something like this for your completion percentage:

    ( SUM(COMPLETED) FIXED(BY GROUP_1, DATE) ) / ( SUM(EXPECTED) FIXED(BY GROUP_1, DATE) )

    Then you could create BeastModes with this same formula wrapped in Max, Min, Avg, etc.

    It sounds a bit like Spaghetti to me, though with 4 lines x number of groups. Maybe you could do vertical box plots, which shows that 4 number summary and put a control/filter to switch between groups.

    image.png

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • random_user_098765
    random_user_098765 Member
    edited November 2023

    Hello, thanks for the response! I am getting the following message. An issue has occurred during processing. We are unable to complete the request at this time. The graph should show a line plot for each month with 1 line for the max, another for the average, and finally a line for the min. Unfortunately, I have no control in setting the parameters for the type of graph to show as I was directed to choose a line graph specifically by the organization that I work for.

  • random_user_098765
    random_user_098765 Member
    Answer ✓

    Hi, your answer was correct

Categories