Summary Fields for Dashboard based on dropdown values

SueinSpain

Hi All,

I have a rewuirement and, although I have a solution, it is very manual. I was wondering if there was a better andmore efficient way of doing what I needed.

It is a question of reverse engineering in that my requirement starts with the end product a dashboard. On this dashboard I wish to show, in graph format, how many failed delivery records there are for each depot in the last 7 days.

Currently there are 13 depots

So in my smartsheet I have 13 summary fields, one for each depot, with a formula to count if the record was created in the last 7 days.

I have several other things to summarise by depot but this is just the simplest example.

All is fine until another 3 depots are added and now I have to add another 3 summary fields for each of the things I want to count.

The depots are in a dropdown, and this is now updated using data shuttle, meaning I can keep my dropdown up to date but still have to manually add my summary fields.

😎 so brilliant can anyone think of an easier way to do this as I'm about to create a new dashboard with 3 counts x this month, last month & previous month x depots (currently 13 + grand total) = 126 summary fields I need to create and add formula to.

🙄my concerns are initial volume to create but also how to keep up to date to match the values in the dropdown

I appreciate there may not be a solution but just thought I'd ask incase I'm just stuck in the longwinded method and there is a better way to achieve this.

Many thanks all in anticipation

Sue

Paul Newcome

I would suggest a row report. You can pull in only the depot column, create a filter to only show rows within the past 7 days, group it by the depot column, then use the Summarize feature in the report to generate the count for each depot. Using this report to populate the chart will make the chart dynamic so you no longer need to manually adjust anything.

Paul Newcome

I would suggest a row report. You can pull in only the depot column, create a filter to only show rows within the past 7 days, group it by the depot column, then use the Summarize feature in the report to generate the count for each depot. Using this report to populate the chart will make the chart dynamic so you no longer need to manually adjust anything.

SueinSpain

OMG Paul you are a genius - this totally works and will save me hours and from going crazy. Thank you so much

SueinSpain

OMG Paul - you are agenius and this totally solves my problem, saves me hours of work & frustration and stops me from going completely mad. HUGE thanks