Hello Smart people,
I have a solution that uses four sheets.
The first, is an export of vehicle assignments from a vehicle management system. Data Shuttle overwrites all data in this sheet at 12:00AM each day.
The second sheet (Daily Rental Summary), is a sheet to aggregate daily totals. A workflow adds a new row to this sheet each day:
The third sheet (Daily Rental Summary Median Values), calculates a weekly Median value of the daily totals in the Daily Rental Summary sheet.
Example formula (Scissor Lift Column):
=MEDIAN(COLLECT({Daily Rental Summary - Scissor Lifts}, {Daily Rental Summary - Week Starting}, [Week Starting]@row))
The fourth sheet (Rental Metrics Reporting) is used to support a rolling 2 year timeframe graph.
Example formula (Scissor Lift Column):
=INDEX({Daily Rental Summary Median Values - Scissor Lift}, MATCH([Week Starting]@row, {Daily Rental Summary Median Values - Week Starting}, 0))
The fourth sheet data is presented in a graph on a dashboard:
Recently, the graph started breaking each day. On the third sheet, the Median() columns started showing #UNPARSEABLE:
After 5-10 seconds, the values would appear.
I'd like to have this run by itself, once again.
A couple things I've tried:
- Adding workflows to force an update on every row with today's date in a "Today" column.
- Combining the Sheet 3 MEDIAN() columns to Sheet 2.
- Revising the daily summary formulas from COUNTIFS() to COUNT(COLLECT())) as shown above.
At this point, I'm stuck. Any suggestions would be greatly appreciated.
Thanks!
Todd
