Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Percentage of variable corresponding to percentage...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Showing results for

Anonymous

Not applicable

2018-01-18
08:35 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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*:

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.

686 Views

1 Solution

Accepted Solutions

sunny_talwar

MVP

2018-01-18
12:55 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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)**

579 Views

3 Replies

sunny_talwar

MVP

2018-01-18
12:55 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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)**

580 Views

Anonymous

Not applicable

2018-01-19
03:29 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

579 Views

sunny_talwar

MVP

2018-01-19
01:23 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Add RowNum in the script using RowNo() function?

579 Views

Community Browser