3 Replies Latest reply: May 22, 2014 4:09 AM by Erica Whalley

# Cumulative Average & Count

I have some data with 2 dates.  I need to calculate the time between them, which I can do, however I also need to show the cumulative average of the time it is taking for the process to complete.

I cant get any of the cumulative totals or averages to work.

I think it is because there are some fields which have not completed the process, however I don't need to count these as I only need to only count the average time taken for ones which have completed?

Any suggestions?

• ###### Re: Cumulative Average & Count

Hi Phil

I think the second dimension is confusing the accumulation option in the expressions box. You can try using a combination of Rangesum() and above() functions to get the desired result.

eg

rangesum(above(total count(Process_Start),0,RowNo(total)))

returns the count of the process_starts cumulatively (without having the accumulation on as an option in the expressions dialogue)

You can then tweak this to get the other results.

something like

=rangeavg(above(total NetWorkDays(Process_Start,Process_End),0,RowNo(total)))

for the averagetime

Letme know if this works,

Regards,

Erica

• ###### Re: Re: Cumulative Average & Count

This works, but there is a snag.  As there is a lot of data I need to group my table by month, and when I do this I lose the cumulative average.

I have attached an example

• ###### Re: Re: Cumulative Average & Count

Can't check this out in depth just yet but it might be becaues of the "total" function (aggregating it over the whole table instead of month)

Try replacing

...Total...

with

...total <Month>....

In the functions. This will evalutate the function over all the values in the group betewen the <>, ie by month

Regards,

Erica