How can I create a quarterly dashboard?
Currently I have an annual dashboard created by mostly pulling multiple reports that primarily uses sheet summary data.
How can I pull quarterly reports using the dates 10/1/22-1/31/23 to filter my reports? Keeping in mind that I can't filter by columns because I am using sheet summary to build my reports.
Here are screenshots:
I want to pull "Screening Date" between 10/1/22-1/31/23
Ideally we can filter by quarter on our report:
I am open to other ideas as well. This is just ONE report from the many reports that are pulling into a single dashboard. So if there's an easier way to filter by quarter on the dashboard itself, that would be the most ideal and efficient manner to do so. Screenshot of the dashboard below:
Thank you!
Answers
-
If I'm understanding correctly:
I would create what I call a "config" sheet that would store the dates you want to limit the search by (Start Date - 10/1/22 and End Date 1/31/23) and then I would reference those in your Sheet Summary fields using a COLLECT, SUMIFS, or COUNTIFS (depending on what you are doing) - if you are doing the same thing in multiple reports - you can reference the same config sheet - and then when the time needs to roll over - it will just need to be changed in one spot.
If the Screening Date is a column in your sheet - then I would do something similar - but with a Helper column to include the items that are within the date range.
If I didn't understand correctly... let me know!
-
Thanks! @isa.ohara does that mean that I would have to change the formulas in EVERY sheet summary formula to reference the "config" sheet? See below:
-
I didn't create the config sheet yet, but if it's the case that I would have to adjust the formulas in each of the sheet summary formulas, it might be too large of a manual task, as we would have hundreds of sheet summary formulas to change.
-
Yes, it would mean changing them in each sheet... if you have control center - it may be worthwhile doing a global update and adding the columns into the sheets (if you have the dates on the rows already).
The challenge with Sheet Summary fields is you can't update those with Control Center Global Updates... perhaps a future enhancement from Smartsheet?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 462 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!