Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Partner
Partner

Percentage of variable corresponding to percentage of other variable

I have two numerical variables, and would like to calculate the percentage of one variable corresponding to at least 50% of the other variable's sum.

For example:

AB
28
120
312
54
27
111
45

Here, the sum of column B is 68, so I'm looking for the rows (in B's descending order) where cumulative sum is at least 34.

In that case, they are rows 2, 3 & 6 (cumulative sum of 45). The sum of these row's column A is 5.

Therefore, the result I'm looking for is 5 / 18 * 100 = 28%


I'm rather new to Qlik, so do not know where to start to implement this.

1 Solution

Accepted Solutions
Highlighted

Re: Percentage of variable corresponding to percentage of other variable

Do you have QV12.1 or above? If you do, you can use this approach

Recipe for a Pareto Analysis – Revisited

=Sum({<RowNum = {"=Aggr(Alt(Above(TOTAL RangeSum(Above(TOTAL Sum(B), 0, RowNo(TOTAL)))), 0), (B, (=Sum(B), DESC)), RowNum)/Sum(TOTAL B) <= 0.50"}>}A)/Sum(A)

View solution in original post

3 Replies
Highlighted

Re: Percentage of variable corresponding to percentage of other variable

Do you have QV12.1 or above? If you do, you can use this approach

Recipe for a Pareto Analysis – Revisited

=Sum({<RowNum = {"=Aggr(Alt(Above(TOTAL RangeSum(Above(TOTAL Sum(B), 0, RowNo(TOTAL)))), 0), (B, (=Sum(B), DESC)), RowNum)/Sum(TOTAL B) <= 0.50"}>}A)/Sum(A)

View solution in original post

Highlighted
Partner
Partner

Re: Percentage of variable corresponding to percentage of other variable

Thanks a lot Sunny!!

No, I'm working on QlikSense 11.24.1.

It seems to work with the file you attached.

Now I just need to adapt it to my data because unhappily, A is not a field, but a calculated measure, and also because I don't have a column RowNum.

Highlighted

Re: Percentage of variable corresponding to percentage of other variable

Add RowNum in the script using RowNo() function?