Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

philmywallet
New Contributor III

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?

Please see attached

Any suggestions?

3 Replies
Not applicable

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

philmywallet
New Contributor III

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

Not applicable

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

Community Browser