Line graphs with multiple averages

[hirereach]

I’m trying to create a line graph with 7 percentage value/columns that have one or more of 5 rolling dates (from another column) on the x axis. It’s adding the values from each of the 7 percentage values instead of giving me the average value for each single date. How do I get the average value to show up for each of those 7 columns, instead of adding the values together? I’m probably messing something up that’s very basic.

[ThinhDinh]

So dates will be dynamic, and you have 7 columns that you want to have an average to show on the graph? Can you show me how you structure your data?

[hirereach]

I have an Airtable table {PeopleSessions-Table} where each row contains a formula column with a percentage-type Score that a Person for each Session they attended. This table is linked to a {Sessions-Table} and a {People-Table} and a {Company-Table} and a {CompanyDepartment-Table} so each row of the table has a RecID for each of those tables that points back to the right Person, Company, CompanyDepartment, Session.

Each Person can attend as many (or few) Sessions as they want in any given month, so it may look like:

Person:

Jan 2023:

• Jan 12 2023 - Score 78.4% in {PeopleSessions-Table}
• Jan 23 2023 - Score 84.2% in {PeopleSessions-Table}
• Jan 29 2023 - Score 91.1% in {PeopleSessions-Table}

Feb 2023:

• No sessions

Mar 2023:

• Mar 14 2023 - Score 96.4% in {PeopleSessions-Table}

Apr 2023:

• Apr 2 2023 - Score 67.3% in {PeopleSessions-Table}
• Apr 15 2023 - Score 82.8% in {PeopleSessions-Table}

I want a line chart of the monthly average in chronological order (but only have the month as a label) like this:

A) Person Chart:

• Jan: 84.6%
• Mar: 96.4%
• Apr: 75.0%
• …

Then, I also want to chart the following in a similar way:

B) CompanyDepartments Chart (per month average for all people from that company department-a subset of the company)

C) Company Chart (per month average for all people from that company)

D) Company+Departments Chart (there are between 1-6 departments for any company and I want to plot each on the same graph as the company)

Similar to the chart below but with a % scale at the left.

Does that help clarify?

[ThinhDinh]

Thanks, I think it’s clearer now.

So, all scores are stored in PeopleSessions-Table and basically you want to roll it up in different levels.

• Create a math column in the PeopleSessions table: YEAR(D)*10^2+MONTH(D) to get a numerical format of the date recorded there (e.g: 202409).
• Create a Format Date column to format the date recorded to its month version (e.g: Jan 2023, Feb 2023).
• Now, for the person level: create a query column in PeopleSessions, target the whole table, filter by person ID is this row > person ID and month number (what we calculated above) equals to this row > month number.
• Use a rollup on top of that query to get the average score.
• Add a single value column to get the first rowID from that query.
• In your chart, filter by rowID is single value > rowID above (sort of a “unique” function to get only 1 row from 1 month, preventing the sum function to happen), X axis will be month names, Y axis will be the average rollup values.

Do the same for other levels, make sure you get department IDs, company IDs etc in there.

@hirereach

Company+Departments Chart (there are between 1-6 departments for any company and I want to plot each on the same graph as the company)

Except this I guess, because this is multiple levels rolling into one. I don’t see a clear way to do that immediately, maybe aggregating different levels into JSONs in the user profiles, and use Quickchart/Chart.js to do it.

[hirereach]

Thank you so much! I don’t quite understand the following, because if we are at the Person level, I don’t have a unique month number in that row to match with the month number from the PeopleSessions table.

@ThinhDinh

and month number (what we calculated above) equals to this row > month number.

I understand the rest (thank you) except not that part, above. What am I missing?

[ThinhDinh]

I mean doing that all in the PeopleSessions table. I assume you should have all of the info in that?

[hirereach]

Thanks for the clarification. I have that set up, but I’m still stuck on some of my percentages hitting 400%+ with this structure as I understood it. What am I doing wrong?

In my PeopleSessions table, I have set up the Query columns and the Rollup columns to deliver the averages. None of those averages exceed 100%, so they seem right.

So I must be messing up the Single Value Row ID steps that you suggested…

1. In my PeopleSessions table, I have a Single Value column pulling the first Row ID value from the Query column (i.e. your example it’s the People Query column)
2. In my Chart that pulls the PeopleSessions table, I’m filtering by PeopleRecID is Screen’s PeopleRecID, and
3. the Single Value Row ID column that I created is Row ID

What am I doing incorrectly?

[ThinhDinh]

Doesn’t sound like you’re doing anything wrong. Can you send some screenshots of your setup so we can verify?

[hirereach]

Sure. Here’s the percentage average column:

Here’s the Single Value column:

Here’s the graph of the percentage average column(s):

Here are the graph filters: