Window Function on Fixed Column Finding Aggregations of Previous Beast Mode

random_user_098765 · 2023-11-09T19:54:03+00:00

There was an error rendering this rich post.

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?

Find more posts tagged with

Beast Mode

Accepted answers

random_user_098765

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

Hi, your answer was correct

All comments

DavidChurchman

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"?

random_user_098765

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

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

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

DavidChurchman

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.

random_user_098765

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

Hi, your answer was correct