topic Re: Sort top 10 by Month in QlikView

Sort top 10 by Month

Tue, 18 Dec 2012 17:16:58 GMT

I have a chart that where I am trying to show the top 10 decending counts per month. as in image

So the question is how do I select and sort so that each month has the months top 10 Items - is this possible? I have tried all kinds of aggrigations in the sort but suspect that I need to somehow select the top ten by monthin the expression instead of accross all the data, but not sure how to do this.

Any help appreciated

Re: Sort top 10 by Month

Gysbert_Wassenaar — Tue, 18 Dec 2012 17:51:50 GMT

I don't think this is possible. As far as I know the order of the bars is always the same for every value of the axis dimension. So sorting within a month isn't possible.

Re: Sort top 10 by Month

vgutkovsky — Tue, 18 Dec 2012 18:31:07 GMT

You can control the dimension so that each month will only have bars for the top 10 value, but Gysbert is right that you can't sort it within each month by default. If you're still interested in going that route, you would create the following calculated dimension and check the "Suppress When Value is Null" checkbox: =aggr(if(rank(sum(Value),4,1)<=10,Dimension),Month,Dimension)

Alternatively, you can get the behavior that you want by actually creating 10 expressions. See attached.

Regards,

Vlad

Re: Sort top 10 by Month

Wed, 19 Dec 2012 17:37:34 GMT

Thank you for your replies.

Vlad I have your example and I see that it works but can't seem to implement successfully for my chart.

foy your chart you have :

aggr(

if(rank(sum(Value),4,1)=1,sum(Value)),

Month,Dimension

)

for mine I have :

aggr(

if(rank(count([SR Close Timestamp]),4,1)=2,count([SR Close Timestamp])),

[SR Close Fiscal Month Name],[SR Resolution Code]

)

for each of the ites( I only added 3) but this is what I get:

I guess I am doing something worng here but cant see it.

Jim

Re: Sort top 10 by Month

Anonymous — Wed, 19 Dec 2012 18:39:00 GMT

Jim,

Not sure if it is what you need, but it may give you an idea...

I had a similar requirement, to show top 5 products by week, and order is different in each week, depending on rank wihtin a week. The difference is I had a pivot table rather than bar chart. And, I had to show the product names in the table, not any numbers.

Solution:

Created a field "R" as a logical island, with values from 1 to 5. Used it as the first dimension, and Week as the second dimension. Expression was like this:

aggr(if(rank(sum(Amount))=R,Product),R,Week,Product)

Formatted using Weeks as columns.

Regards,

Michael

Re: Sort top 10 by Month

vgutkovsky — Wed, 19 Dec 2012 22:06:41 GMT

Michael, very neat suggestion, I like it!

Jim, you can try to modify my solution to implement Michael's island field, which should work well. First, keep in mind that the only chart dimension has to be Month, not the second dimension. Also, in the example I posted, expand the expressions and note the Background Color expressions that have been defined. My solution requires that your second "dimension" (which is really an expression) have static coloring set in the script; the expressions also cannot appear as a legend, so if you need to have a legend, I suggest you create a straight table like the one in my example.

Regards,

Vlad

Re: Sort top 10 by Month

jagan — Thu, 20 Dec 2012 08:17:35 GMT

Hi,

PFA file, I managed to get the top 10 by month and ordered in descending in Straight table by using single expression. But it is not accurate in Line chart.

Please check, hope it may be helpful for you.

Regards,

Jagan.