Cumulative Average & Count

[Anonymous]

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?

Please see attached

Any suggestions?

[#2]

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

[Anonymous]

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

[#4]

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