Averages on a Formula Indicator

[Josh Cooper]

I recently had a question come up from one of my customers, and it seemed like it could be a bit tricky, so I thought I'd put it out here for consideration as well.

They were looking at the "Average Time to Resolve an Incident" indicator - when they changed it to By Month Sum +, it jumped to a huge number and they wanted me to make sure it was working.

After considering it for a bit, I understood what it was doing - I had gotten caught up in their way of thinking during the explanation, but when you ask for a by Month Sum +, it's literally adding up the results it receives for each day.   So if Day 1 is 2 hrs, Day 2 is 3 hrs and Day 3 is 6 hrs, then the by Month Sum + on Day 3 would be 11 hrs.   Which is exactly what they asked for, but not very useful.

I determined that any such sum wouldn't be very useful, so I went and changed it to a by Month Avg+ and added all the Sums for to the excluded time series for that indicator.   But after thinking about it for a bit, actually as I was documenting the solution, I realized that wasn't the right answer either.   Because again, it's only working with the products for the time series aggregations.   So in our example above, if Day 1 is 2 hrs, Day 2 is 3 hrs, and Day 3 is 6 hrs, then the by Month Avg + on day 3 would something close to 4hrs.   That's a lot more believable number, which is why I almost didn't catch it.

The problem when you do the Average of an Average, you lose the scale -   it works fine if every day is the same, but if Day 1 is 100 tickets, Day 2 is 100 tickets, and Day 3 is 1000 tickets, then the real average time is a lot closer to 6 than it is to 4.

So the right answer is to recreate a Formula indicator (insert and stay on the existing one) and name it to "<Indicator> Monthly Avg" then use the same indicators, but force the 'by Month Sum +' time series on each.   Now the results you get will be the most accurate.

[Daniel Oderbolz]

Dear Josh

Did you ever find a solution to this issue?
We are having a similar requirement - we want to add the result of a formula.

Thanks a lot & Best
Daniel

[Josh Cooper]

As Jason pointed below, there's the option to 'Apply time series to result' which may make things much easier, but in my head I'm having trouble reconciling how that prevents the average of averages type of issues that you get by losing focus of the weighting. 

I.e. Percent of incidents that met SLA:  Jan 1 98/100 is 98%, Jan 2 is 10/10 100%, Jan 3 is 960/1000 is 96% - the by month average is going to be 98%

You can try doing the same and checking the "apply time series to result" and see if it handles the weighting for you, but worst case you can still handle it by applying the "by month sum +" to each component of the indicator, so that you only get the one computed average score per day based on the full weighting.

That way instead of taking the time series 98+100+96 / 3 = 98%, you calculate out (98+10+960) / (100+10+1000) = 96.2%

[Jason Monzo]

Hi Josh,

Are you familiar with the "apply time series to result" checkbox on formula indicators?  I think it will help you greatly here.  When it isn't checked and you apply a time series to a formula indicator, the time series gets "injected" into the formula.

So, if the formula is:

[[Summed duration of closed incidents]] / [[Number of closed incidents]]

Then, applying the "By month SUM" time series effectively does the following.

[[Summed duration of closed incidents / By month SUM]] / [[Number of closed incidents / By month SUM]]

But, if you check the "apply time series to result" (ATSTR) checkbox, then the time series will not be injected into the formula.  The formula will be calculated as originally written and the time series will be applied to resulting set of scores/values.

So, if the formula above produced a daily average duration of closed incidents and I wanted a monthly average, the simplest solution would be to check the ATSTR checkbox for the formula indicator and then apply the "By month AVG" time series.

Incidentally, if ATSTR were not checked, then the "By month SUM" time series would also provide a monthly average.

Regards,

Jason Monzo

[Josh Cooper]

You're absolutely correct, and at the time I didn't know about the ability to apply specific time series to only the result.

Thanks for helping keep the answer up to date!