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

Member

Hello, this is a continuation of

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.

Tagged:

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

• Coach

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`)

• Member

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?

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

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.