Dashboard with quarter stats

Majkoss

Ok so im having project tracker file and there's over hundred projects yearly. So The goal is to make a chart that shows how many projects per quarter are ongoing. So only 4 quarters are needed. The one that we are in, the second, the third and 4th of 2022. But when we move on, so for example in May or June, the chart should update the quarters (so w'd be Q2 2022, Q3, 2022, Q4, 2022, Q1 2023). How to create such a chart?

boscod35

Hi Majkoss,

I ran into a similar issue with creating the reports for our management dashboard. This can be achieved in two ways.

Report Method:

1. With the tracker that you have (raw data) - Create a report how you would like to visualize in the dashboard.
2. Call the report to the dashboard that will summarize the project information.

Creating a metadata file method:

1. Create metadata - it is a kind of metric that you would like to call into the dashboard
2. in the dashboard view create widgets and multiple charts/scorecards can be displayed.

Hope this helps,

Happy Friday!

Don.

Majkoss

Not really solving my problem with quarters. I got to do exact same as here:

boscod35

Hi Majkoss,

As suggested, for me quarterly metrics work with the second method. The below Screenshot we wanted to visualize in the form of Baseline/Forecast/Actuals for management visibility. To achieve this we created a metrics sheet and prepared a logic, which you see as like in the metadata. One layer above these raw data, logic to be prepared then it has to be called to the dashboard as like you have presented.

Create a metrics sheet and with the formula, "Countif" call the reference sheet and set the criteria. this will populate the required information. Then, create the Dashboard and call every single widget for the scorecard, and reference them to the metric sheet.

Hope this helps to solve the puzzle.

Thanks

Don Bosco

Majkoss

Hi

thank you for help but that is not solving the problem of the rolling format. Because this should be only 4 last quarters. So that means 3 months from now the dashboard should exist of Q2 2022, Q3 2022, Q4 2022 and Q1 2023. And 3 years later it should be also automatically updated to Q2 2025, Q3 2025, Q4 2025, Q1 2026.

Sandra Guzman

Hi @Majkoss,

In order to display a rolling quarter view in your dashboard there are a couple of things you will need to consider.

If you don't currently have any add-ons, your first option would be to adjust the reporting data manually. You would have to update the report/chart widget in the dashboard in order to display the four quarters in the manner in which you described on a rolling quarter basis. Even if you were to try and get fancy with automation to pull data based on certain quarters, you still need to pull through the updated report. You could alternatively, generate a report that is pulled through on the dashboard and you could essentially update the filters in the report so you don't have to do anything to the dashboard. In the end, these all require manual adjustments.

You could explore the use of Spotfire or Tableau. This will give you the ability to provide more powerful analytics and would allow for the customization of views.

https://www.smartsheet.com/marketplace/apps/tibco-spotfire

https://help.smartsheet.com/articles/2476166-smartsheet-live-data-connector-connect-smartsheet-and-third-party-tools-via-ODBC

All the best,

Sandra