topic Cumulative Average & Count in QlikView

Cumulative Average & Count

Anonymous — Wed, 21 May 2014 07:51:11 GMT

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?

Re: Cumulative Average & Count

Thu, 22 May 2014 05:54:44 GMT

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.

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

Anonymous — Thu, 22 May 2014 06:37:57 GMT

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

Thu, 22 May 2014 08:09:09 GMT

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