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

random_user_098765
random_user_098765 Member
in Charting

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.

Tagged:

Best Answer

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    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.

Answers

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

    If I solved your problem, please select "yes" above

  • random_user_098765
    random_user_098765 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?

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    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

    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

    Thank you!!

Categories