Metrics in play include:
# Incidents_Recordable: SELECT IFNULL(COUNT(incidentID),0) WHERE isRecordable=1 AND Date (Event) BETWEEN _Timeline_Start AND _Timeline_End
YTD Recordables: SELECT IFNULL(RUNSUM(# Incidents_Recordable), 0) BY Month (Event) WHERE Date (Event) BETWEEN _Timeline_Start AND _Timeline_End
Here is the data table where the issue manifests itself:
The highligted elements in the data table show the YTD count dropping to zero, rather than preserving the YTD from the previous period. Cleveland, for example, should remain a 2 through March and April, as the YTD count was 2 at the end fo February. I’ll note that if Cleveland had an incident in May (outside the range of the image), the YTD count would (and does) correctly reflect the value of 3. Analysis shows that these “gaps” occur whenever a month has no incidents (and there is at least 1 incident in a predecessor month).
It is clear that the RUNSUM function works correctly when there is an essentially continuous time series (e.g, see YTD hours), but seems to break down if the data stream is iintermittent.
Any thoughts and suggestions would be greatly appreciated.
Best answer by Joseph HeunView original