Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
A | B |
---|---|
2 | 8 |
1 | 20 |
3 | 12 |
5 | 4 |
2 | 7 |
1 | 11 |
4 | 5 |
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.
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)
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)
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.
Add RowNum in the script using RowNo() function?