How can I create a quarterly dashboard?

Options

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

  • isa.ohara
    isa.ohara ✭✭✭✭
    edited 12/09/22
    Options

    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!

  • pothosplant
    Options

    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:


  • pothosplant
    Options

    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.

  • isa.ohara
    isa.ohara ✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!