I would like to create a KPI on the dashboard that averages how long it takes for a cell to change. Can someone advise on how to best approach this? This would be greatly appreciated.
I have a table that houses all of our anomalies, dispositions, etc, for our manufacturing site. When an anomaly is reported, an anomaly table record is created, and its status is set to Open. I would like a KPI that shows the amount of time it took for that status to change from Open to something else (dispositioned, closed, etc).
Hi Levi -
Couple questions for you:
- How is the table structured? Is there a separate field/column for initial status and final status (ex. open → closed) A screenshot may be helpful!
- Is there a field/column or a variable that tells use the time in between when the status first was “open” to when it changed? That will be the variable of interest needed for your analysis
Thank you for the reply! I unfortunately do not have separate columns for status changes, but I do for when they change:
I am struggling now to find the average value of these to create the KPI:
Thanks for the additional info! It seems like what you want is the difference between “Date Created” and “Date Reviewed” in days, and then the ability to understand the average number of days it takes for this status change within a given time period for your KPI?
I think what you have is very close! You can actually add an “average” operation before your expression and it will take an average over the date range you select from the left hand panel.
Is this what you are looking for?
Thanks for the response. When I lengthen my date range to All Time, the average breaks. Am I inputting something incorrectly here?
When you change the date range settings to “all time” does the average break, or does it just show the average for all IDs within that time range?
I’ll go through an example using the following sample table:
In the analysis below, the data is Grouped by ID, calculating two interval averages: between date recorded and date reviewed + date reviewed and date completed. The table template displays the average time per record (ID). I converted the interval to seconds and rounded it to the nearest day w/ 2 decimal points.
Here is what one average looks like in a bar chart visualization. Because we grouped the data by ID,there are bars for each ID in the table displaying the average time per record:
And if you want the total average, you’d aggregate it into a single number visualization:
I apologize for the delayed response. Changing the date range to “All Time” does not break the average, it just shows the average for all IDs within that time range. I would like the total average for the last month moving forward, but wanted to test it out with All Time first. I don’t seem to have “Single Value” as an option however:
Please let me know what I may be doing wrong. Perhaps I’m using the wrong template?
Hi @Levi, with regards to the single number analysis, try changing the template type (circled in screenshot). When you click it, you should get the option to choose "single number’.
Thank you! One last question… am I inputting the round function incorrectly?