topic Re: Sum Aggregation Problem in QlikView

Sum Aggregation Problem

Wed, 25 Sep 2013 15:45:29 GMT

I have the first to columns below, and need a way to create the third column. The sum column is the percentage of scores divided by the total number of records. I need the third column to be the rank order of the score percentages. In other words, 100% of scores are 1 or higher, 97% are 2 or higher, and so on.

Score	Sum(If(X),1,0)	Need
1	0.03	1.00
2	0.02	0.97
3	0.05	0.95
4	0.06	0.90
5	0.07	0.84
6	0.10	0.77
7	0.09	0.66
8	0.08	0.58
9	0.09	0.50
10	0.07	0.41
11	0.04	0.34
12	0.05	0.31
13	0.06	0.26
14	0.09	0.19
15	0.06	0.11
16	0.03	0.05
17	0.01	0.02
18	0.01	0.01
19	0.00	0.00
20	0.00	0.00
21	0.00	0.00

Re: Sum Aggregation Problem

swuehl — Wed, 25 Sep 2013 15:50:39 GMT

Not sure how you get the second column, it would be good to get some input data as well, or even better, post a small sample app.

In general, you can get a cumulative sum of your second column using rangesum(above( EXPRESSION,0,rowno() )),

so try something like

=1.00 - rangesum(above( YOURSECONDCOLUMNEXPRESSION ,0,rowno()) )

edit: if you want to exclude the current dimension line from your aggregation, use an offset of 1:

=1.00 - rangesum(above( YOURSECONDCOLUMNEXPRESSION , 1,rowno()) )

Re: Sum Aggregation Problem

Wed, 25 Sep 2013 16:08:13 GMT

Thank you very much. That is exactly what i needed.