Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Filtering based on cumulative sum

Hi,

I have a table which is:

Name     Value

a             10

b             20

c             30

d             40

Now In a table I want to list the names of people whose cumulative value is not more than 80% of the total value of all people.

To explain further:

The total value for a,b,c,d is 100.

80% of 100 is 80.

So the output should be (Sorted with the highest value coming first)

Name     Value

d             40

c             30

Note: It did not display b since if b were put the cumulative value of d,c and b would be 90 which is greater than 80%

Any help would be appreciated.

Regards,

Rajiv

3 Replies
Highlighted
Partner
Partner

Filtering based on cumulative sum

Hi, Rajiv,

look at actions and selections: Pareto

Greetings from Munich

Martina

EVACO GmbH

Highlighted
Partner
Partner

Filtering based on cumulative sum

Hi Rajiv

I didn't quite understand why B is comulitive value of D+C rather than A+B, but Anyway:

You can use the following Aggr functions to calculate the comulitive sum of each line:

rangesum(above(sum(Value),0,RowNo()))

Then you divide it by total Sum and you receive the Percent.

After you get the Percent you can just filter by it.

Hope this was helpful.

Ori

Highlighted
Not applicable

Filtering based on cumulative sum

Hi Ori,

Your rangesum function was useful. However, I was still not able to get the solution.

I have an additional requirement also.

I have the raw data as below:

Name  SubName    Value

a               i              3

a               ii              3

a               iii             4

b               i              6

b               ii              6

b               iii             8

c               i              9

c               ii              9

c               iii             12

d               i              12

d               ii              12

d               iii             16

Required Output:

        i        ii        iii     Tot

d      12     12      16     40

c       9      9        12     30

b       6      6         8     20

Tot   27     27       36    90

Logic:

The name fields are 1st sorted in descending order according to the sum of 'value'

Sum of 'value' is 100. 80% of 100 is 80. So only those 'names'  should appear whose 'value' sum up to 80 or just go over 80.

Basically:

sum of d is 40 so 'd' is displayed

sum of d&c is 70 so 'd' and 'c' is displayed

sum of d&c&b is 90 so 'd', 'c' and 'b' is displayed.

Now since the cumulative sum has exceeded 80, no other 'name' gets displayed.

Finally the 'name' value are split 'SubName' wise.

If someone could give an application it would be great.

Regards,

Rajiv Maskara