Filtering Summary Reports

Question

Hello!&nbsp;I have a table with the following fields:

Name (text)
Check-in Period (date)
Pulse (Numeric)

I would like to have a report that shows ONLY employees whose average pulse over the current month and previous 2 months is less than or equal to 3.&nbsp;&nbsp;

Currently, I've set up a summary report (see Current Summary Report &amp; Current Summary Report Settings in attachment) to show the average pulse for the current month and previous 2 months.&nbsp;
I'm struggling to filter the summary report to only show records where the average pulse is less than or equal to 3 (see Current Summary Report - I would like Employee 4 to be excluded since their average is above 3).
Using the normal filter options, if I set up an additional filter to include only records with a pulse of less than or equal to 3, it filters out the individual records so they aren't included in the summarized data. For example, in Drilldown Report into Employee Name 2, the record on 03-26-2022 with a pulse of 4 would be filtered out, which would then change the average on the summary report.&nbsp;

Any help would be much appreciated!------------------------------Addie Brunson------------------------------

markshnier__you · Answer

I suggest a different approach.Make a summary field on the relationship between employees and pulse measurements and summarize the Average Pulse Rate for the last three months.&nbsp; The make a standard table report of Employees with filter of [Average Pulse Rate Last three months] is less than or equal to 3.------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

deepaprashant1 · Answer

Hi Mark,&nbsp;I am trying to do something similar but it doesnt involve different tables. The summary in my case if just a count of the number of prompts in a category and Category is just a text field in the table called 'Prompt.' So a summary report, counts the number of prompts in each category and I am also calculating a % in the next column. So I have three columns : Category, # of Prompts, % of total. I want only the top ten or don't mind filtering by percentage. But how do I call upon a summary column to filter?Any help or guidance will be great!&nbsp;

curevick · Answer

Awesome solve Mark! A little more context for Addie:

The issue you’re running into stems from filters applying before the summarization happens, which means filtering out individual Pulse records changes the final averages. That’s why simply filtering for Pulse ≤ 3 removes rows you still need for the calculation.

Some Extra Fun:

Sometimes we use this create summary fields and then push them back down to the child table as a look-up field. This could be used to create rankings, or used to grab things like "latest child record" by using record ID. There are tons of cool use cases using this strategy.

curevick · Answer

Hey Deepak, unfortunately you can’t filter a summary report based on its own calculated summary columns (like % of total) because those values only exist after the report is generated. The filters run first, so there’s nothing to compare against. The workaround is the same pattern as described above: move the summarization into the table level, then filter on it.

In your case, you'll need another table, then create a numeric summary field on Prompt table’s relationship (summarize the number of prompts in each Category). Then push that summary back down as a lookup field. Once the count and percentage exist as actual fields in the table, you can create a normal table report, sort by your percentage, and easily show the top ten or whatever cutoff you want.

Hope this helps!

Forum Discussion

Filtering Summary Reports

4 Replies

Related Content

Report Dynamic filters

Summary Reports

Record picker behavior with preselected values not on filter report

Embedded Grid Edit Report Pre-filtered Conditional Dropdown

QB Dynamic Filters not working in Summary Report.