Show top 10 schools on dashboard based on number of active students at each in datasheet

WillieBNZ

Been playing around for a while and haven't been able to sort....I have a datasheet with a bunch of data associated with a list of students, including the school they attend and whether they are currently active, graduated etc. I am looking to build a dashboard widget that shows the top X schools by count of active students....and would do similar for graduated.

Any pointers?

Thanks,

Willie

WillieBNZ

Excellent - you got me thinking straight, and most importantly simply! I went with:

1) A helper column called "Active Students" using

=COUNTIFS([Contract School]:[Contract School], [Contract School]@row, [Program Status]:[Program Status], "1 - Active"). Even though I filter later on "1 - Active", this puts the active count in a column which allows it to be filtered at this level later.

2) Row report filtered on:

• Program Status = "1 - Active"
• Active Students greater than/equal to 10 (this actually gave me 11 schools but good enough)

3) Grouped By "Contract School"

4) Summary by "Active Students"

5) Plunked the report on my dashboard.

Meets my needs perfectly. Thanks so much Genevieve.

Willie

Genevieve P.

Hi @WillieBNZ

The way I would do this is to set up a helper column in your source sheet with applies a RANK to each of your rows so that you can find the top ten. This would likely be done using the RANKEQ Function:

=RANKEQ([Active Students]@row, [Active Students]:[Active Students], 1)

Then once each row has an associated number, you can create a Report looking at this source sheet. Filter the Report by the helper Rank column to only show the values less than 11, and SORT the Report by that column as well!

Then you will have your top ten in order that you can display on a Dashboard using the Report Widget.

Let me know if this makes sense or if you'd like to see some screen capture examples of what I'm describing.

Cheers,

Genevieve

WillieBNZ

Thx Genevieve - getting there.

I couldn't get the RANKEQ to work as my contract school field text...somewhere I read it just works on numbers. So I added a second (well actually first) helper column I read about in another article.

=COUNTIFS([Contract School]:[Contract School], [Contract School]@row, [Program Status]:[Program Status], "1 - Active")

This gave me the number of students at each school that are currently active.

In the second helper column I used your RANKEQ formula:

=RANKEQ([Active Students]1, [Active Students]:[Active Students], 1)

....and this did rank the schools however instead of rankings from 1-100ish (the number of schools in question), it does so based on total students in the overall program (600ish). So my top school with it's 40ish students shows 594 instead of 1 (or 100). I'm guessing there is a fairly simple way to address this.

Once I get to the report side of things, I think I'll also need to do some sort of DISTINCT function....with my primary field the student name, I'm currently getting 40 lines for the top school, then 33 for the next etc. I just want the report to show one line per school, rather than one per person.

Thanks,

Willie

Genevieve P.

Hi @WillieBNZ

Thank you for clarifying how your data is set up a bit more! Yes, you would need some sort of COUNT formula to first create Numbers for the Rank, however as you've found this will apply a number to each individual row.

What about instead, using a Report to filter by Program Status, then Group by the School, and finally use Summarize function in a Report to Count the rows/students per school? (See: Redesigned Reports with Grouping and Summary Functions). You could then display this Report as a Chart Widget in your Dashboard, although it will display all 100 schools in the Chart.

An alternative would be to have a separate Metric sheet where you list the Contract School name once, then you can do the same calculations but cross-sheet:

=COUNTIFS({Contract School Column}, [Contract School]@row, {Program Status Column}, "1 - Active")

This would only create one count per-school. You can Sort the Metric sheet by this number, then manually select the top 10 rows to display in your Dashboard (or do the Rankeq formula and Report option I mentioned in the first post).

I hope that helps!

Genevieve

WillieBNZ

Excellent - you got me thinking straight, and most importantly simply! I went with:

1) A helper column called "Active Students" using

=COUNTIFS([Contract School]:[Contract School], [Contract School]@row, [Program Status]:[Program Status], "1 - Active"). Even though I filter later on "1 - Active", this puts the active count in a column which allows it to be filtered at this level later.

2) Row report filtered on:

• Program Status = "1 - Active"
• Active Students greater than/equal to 10 (this actually gave me 11 schools but good enough)

3) Grouped By "Contract School"

4) Summary by "Active Students"

5) Plunked the report on my dashboard.

Meets my needs perfectly. Thanks so much Genevieve.

Willie

Genevieve P.

Amazing!! I'm so glad you were able to surface the data you were looking for. Looks good!