Solved: Help using rank outside of a chart - Qlik Community

Report Inappropriate Content · ‎2016-09-20

Hi, I am trying to use the rank function outside of a traditional chart. Let's use a simple Sales example. I have a data set with 6 stores and their sales value.

Store, Sales

A , 50

B , 10

C , 60

D , 20

E , 90

F , 30

I need a chart where I have 6 expressions. 1 for each store showing the rank of that store.

A B C D E F

_____________________________________________

Store Rank 4 1 5 2 6 3

This is a very simplified example of my scenario to stay focused on the challenge at hand. I can not use a Pivot table with 1 expression for a few reasons starting with the fact that I can not get the look my company wants with a pivot table. Once I have a solution I'm happy to post my full scenario and solution but for now I don't want to distract with other challenges (which I think I have all of them solved).

sunny_talwar · ‎2016-09-20

This?

Expressions:

=Only({<Store = {'A'}>}Aggr(Rank(-Sum(Sales)), Store))

=Only({<Store = {'B'}>}Aggr(Rank(-Sum(Sales)), Store))

=Only({<Store = {'C'}>}Aggr(Rank(-Sum(Sales)), Store))

=Only({<Store = {'D'}>}Aggr(Rank(-Sum(Sales)), Store))

=Only({<Store = {'E'}>}Aggr(Rank(-Sum(Sales)), Store))

=Only({<Store = {'F'}>}Aggr(Rank(-Sum(Sales)), Store))

View solution in original post

sunny_talwar · ‎2016-09-20

This?

Expressions:

=Only({<Store = {'A'}>}Aggr(Rank(-Sum(Sales)), Store))

=Only({<Store = {'B'}>}Aggr(Rank(-Sum(Sales)), Store))

=Only({<Store = {'C'}>}Aggr(Rank(-Sum(Sales)), Store))

=Only({<Store = {'D'}>}Aggr(Rank(-Sum(Sales)), Store))

=Only({<Store = {'E'}>}Aggr(Rank(-Sum(Sales)), Store))

=Only({<Store = {'F'}>}Aggr(Rank(-Sum(Sales)), Store))

sunny_talwar · ‎2016-09-20

Now can you explain what the context is

Report Inappropriate Content · ‎2016-09-20

Right!!!! Only... I knew I was forgetting something. I'll get that into my code tomorrow and post it. I have a common situation in my applications where we have % values that are rounded and need to display 100%. I've created a series of variables that can be used to find out how far away from 100% we are and apply the difference evenly to the largest members of the set.

Store, %

A , 20

B , 30

C , 10

D , 18

E , 12

F , 8

Ends up as

Store, %

A , 21

B , 31

C , 10

D , 18

E , 12

F , 8

I need to show these numbers both in a pie chart and as a straight table with the stores (in my example) as columns.

It is more important to our clients that the rounded visible shows 100% than the % actually match the data underneath. I know that doesn't make sense most places but it does here. So what I've done is created some reusable code to get the rounded % value and then how far from 100% we are. All I needed was ranking the the stores so I can apply the difference evenly.

I tried many times to find a smoothing algorithm to find the difference but it wasn't working because of the rounding. My next solution was a bit brute force. I think this one is pretty good.

sunny_talwar · ‎2016-09-21

So is your issue all resolved now? If it is, I will suggest you to close this thread by marking the correct response, but if you still have questions, do let us know.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Report Inappropriate Content · ‎2016-09-21

Yep. just working on some final touches. I've marked your answer as the solution.

Report Inappropriate Content · ‎2016-09-24

I posted a document yesterday with my full solution in it for people to use. It hasn't been approved yet. I'll post here when it gets approved. I'm not sure how to see those items that I've posted and are not approved.