We are trying to figure out how to make a dashboard have selectable date ranges

Jim Price

Please Help

Andrée Starå

Hi @Jim Price

Unfortunately, it's not possible at the moment, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment

As a possible workaround, you could create a solution using a so-called helper form, helper columns, and more to submit the dates and refresh the data to match what you'd want to see.

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Sean Morgan

Hello @Jim Price ,

As of know, there isn't a dedicated feature for having customizable date ranges for data on a Dashboard. With this, please can you raise an Enhancement Request for this great idea here: https://help.smartsheet.com/articles/2476161

Contrary to this, I've seen this is available using Tableau, by creating custom Reports on data, and then having a date range widget within the Tableau Widget. See more on Tableau here: https://help.smartsheet.com/articles/2478366

Let me know if you have any questions!

Regards

Sean

Liz Pittoni

@Andrée Starå or @Sean Morgan - is there a way that you know of to use "date submitted" within a report/ dashboard to easily show the volume of items coming in through the webform over the past 30 days?

Genevieve P.

Hi @Liz Pittoni

Yes! There are a few different ways you can do this, depending on what you want your Dashboard to look like.

Personally, I would set up a separate, metrics sheet to track and create numerical data, so I could use it in a Chart Widget. The way I would do this is to have 2 helper columns - one that lists the numbers 0 - 29, then one that has a simple formula to create the rolling 30 days in a Date Column.

Then you can use a simple cross-sheet COUNTIF formula that Counts how many submissions came into that sheet per-day, based on that Date Column.

Here's an example of that metric sheet:

First Column:

The "Day w/in 30" column is just a manual list of numbers, 0 - 29.

Second Column:

This then allows the formula in the Date column to be:

=TODAY() - [Day w/in 30]@row

So for example, that bottom row (row 30) would be TODAY() - 0

Today - 0 is Today's Date.

Today - 1 is Yesterday's Date, etc.

COUNTIF formula column:

Then you can count how many rows have the submission date that's listed in the Date column:

=COUNTIF({Date Submitted}, Date@row)

{Date Submitted} is a cross-sheet reference to your intake sheet where the form comes in.

Dashboard:

Then you can set this up in your Dashboard using the Chart Widget, depending on what you'd like to show:

TOTALS:

You'll notice I also have a Sheet Summary field set up to create a Total of submissions, if you wish to show this. That's just a SUM of that Number of Submissions column.

Now, I may have made this a lot more complicated than you wanted. If you simply just wanted one COUNT of all the submissions from within the past 30 days, you can use this cross-sheet formula for one summary number:

=COUNTIFS({Date Submitted}, >= TODAY(-29), {Date Submitted}, <= TODAY())

FINAL NOTE:

If you use either of these formulas, you will see that they have the TODAY function to find out what today is and then update what "the past 30 days" are compared to this. This means that the sheet would need to be opened and saved each day to update your dashboard. If your sheet won't be in use that often, you could also set up 2 Automations on your sheet to force it to refresh at sometime in the middle of the night, after midnight has passed.

1. Set an auto-lock row automation for a specific time in the middle of the night.
2. Set an auto-unlock row automation for one hour later.

If I've misunderstood what you're looking to do, it would be helpful to have a bit more information, including an explanation of exactly what you'd like to see on your dashboard.

Cheers!

Genevieve