topic Cumulative Count on multiple dimensions in QlikView

Cumulative Count on multiple dimensions

Tue, 06 Oct 2015 02:15:19 GMT

Hello all,

I am trying to calculate a cumulative count on a straight table with multiple dimensions.

My data is composed of an ID field and 3 dimensions A, B and C.

In my table I calculate the number of occurrences of each pattern A/B/C that I am sorting descending. On top of that I would like to have a cumulative sum of my count(ID), like this:

A	B	C	Count(ID)	CumulativeCount(ID)
M	10	Test1	8	8
F	10	Test1	7	15
F	20	Test2	6	21
M	10	Test2	6	27
F	20	Test1	4	31

But I cannot get this Cumulative Count working. I tried full accumulation option, aggr(rangesum(above... and rangecount too but it won't work.

Also I cannot pre-calculate the accumulations in the script as my columns are added dynamically (using enable conditional).

Does anyone have any idea?

Thank you,

Thomas

PS: I attached an example of my problem in qvw

Re: Cumulative Count on multiple dimensions

jonathandienst — Tue, 06 Oct 2015 05:06:22 GMT

TOTAL is your friend:

=RangeSum(Above(TOTAL count(ID), 0, RowNo(TOTAL)))

Re: Cumulative Count on multiple dimensions

Tue, 06 Oct 2015 05:46:05 GMT

Thank you Jonathan, I forgot about TOTAL.

Unfortunately it did not fix the whole thing, but I now have a better idea of what is the problem:

[=RangeSum(Above(TOTAL count(ID), 0, RowNo(TOTAL)))] makes the cumulative sum correctly but breaks the sort on the count(ID)
[=sum(aggr(rangesum(above(total count(ID),0,RowNo(total))),A,B,C))] partially works too but it uses the order of A,B,C. So when I sort it by count(ID) it doesn't work.

Is there a way to custom sort the temporary table that aggr() is creating?

Re: Cumulative Count on multiple dimensions

sujeetsingh — Tue, 06 Oct 2015 05:48:07 GMT

follow the links please

Cumulative Straight Table