Is there a way to Add Filters to Pivot Tables?
Hi -
I'm in the process of creating a "Request Tracker" sheet with associated reports & pivots to serve up to a dashboard. I want to create a pie chart that breaks down "Request Type" but only for open requests (meaning, the Status is not one of "Completed" or "Cancelled").
So my question is, is there a way for me to create a pivot table with the COUNT of each "Request Type", but also apply a filter by "Status"? The only way I have been able to figure out is to add a Parent/Child row on the pivot table for "Request Type" (parent) and "Status" (child), and then add a filter to the the pivot table sheet (see below). But I'm worried whether or not the filter will stick and serve up the correct metrics to my dashboard.
Is there a better way to do this? The Template Set I downloaded originally used COUNTIF functions to add metrics & graphs to the dashboard, but I'm not sure if COUNTIFS would work in this case (e.g. count if "Request Type" = X and Status does NOT equal one of X, Y, or Z).
I also considered creating a pivot against a report instead, but I had read somewhere that pivots against a reports don't allow the "Update Immediately" option under Execution Frequency (in Pivot App). So I am less inclined to go with this option, but open to it if it's more reliable.
Answers
-
Hi @Marina Iida
My suggestion would be to create the Pivot off of the Report to filter the data, however you are correct that then the fastest frequency for this Pivot to run would be 1-hour instead of immediately.
An alternative would be to set up a helper column in your Destination sheet which has a very simple PARENT formula of
=PARENT(Rows@row)
If you set this to a column formula it will show the Parent Name in your Rows column in each of the Child Rows.
This means that you could create a Report off of the Pivot Destination sheet and Filter by the helper column so it only shows the Status you want.
This would mean that the Report you're using for your Dashboard would be after the Pivot run so that it all flows through immediately. Let me know if this makes sense and works for you!
Cheers,
Genevieve
-
Thank you, Genevieve!
That makes sense! I think in this case, I might opt to go with the option to do a Pivot from the report because 1 hour is more than enough for frequency for my need. But if I ever wanted something more real time, I will keep that other option in mind!
Thank you so much!
-
Yes, that sounds good! Glad I could help 🙂
