Aggregating Numeric Column in a Line Graph over time based on a Different String Column

random_user_098765

Hello, this is a continuation of

https://community-forums.domo.com/main/discussion/comment/81989#Comment_81989

I was wondering how I can show in a line graph for the MAX,MIN, and AVG of a column that is fixed on a different column. More specifically, I want to find the MAX, MIN, and AVG of a 'Completion Percentage' column based on a 'Group_1' column; meaning that I want to show the maximum of the 'Completion Percentage' grouped on the 'Group_1' column with the same for the minimum and average. I want to plot this info on a line graph over time with three lines for each of the MAX,MIN, and AVG, respectively.

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.

ColemenWilson

If I am understanding your question correctly, you would use a beastmode and calculate those values then use those values on a multi-line chart. You'll need to request beta access to nested beastmodes from your CSM:

MAX(`Completion Percentage`)

MIN(`Completion Percentage`)

AVG(`Completion Percentage`)

random_user_098765

Hi Coleman, thanks for responding. I am looking for the maximum, minimum and average of the 'Completion Percentage' that is grouped on the 'Group_1' field. Do you have a suggestion on how to do that?

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

Thank you!!