topic Re: Re: Re: Bottom 5 values in QlikView

Bottom 5 values

Anonymous — Tue, 15 Apr 2014 07:38:37 GMT

Hi,

I have two charts that shows (or are supposed to show) top 5 and bottom 5. The top 5 is no problem, here's my expression in the dimension:

=aggr(if(rank(sum({$<AccountName={'3010'}>} Value))

<=5) ,Product),Product)

And for the bottom 5, i thought that this would work:

=aggr(if(rank(-sum({$<AccountName={'3010'}>} Value))

<=5) ,Product),Product)

My problem seem to be the "-" sign in front of sum. Aggr doesn't seem to like it.

Any ideas of how i can achive this? Dimension limit is not an option here since the dimension i want to rank by is the secound dimension after a cyclic group.

//A.

Re: Bottom 5 values

tresB — Tue, 15 Apr 2014 07:44:38 GMT

It seems okay. Could you share sample qvw that demonstrates the issue?

Re: Bottom 5 values

er_mohit — Tue, 15 Apr 2014 07:46:46 GMT

sorry to edit up try this

=aggr(if(rank(-sum({$<AccountName={'3010'}>} Value),4)

<=5 ,Product),Product)

Re: Bottom 5 values

Anonymous — Tue, 15 Apr 2014 07:51:14 GMT

Tried that, doesn't work unfortunatly.

I'm thinking maybe the set analysis is the problem since it works if it isn't at set analysis that's beeing ranked and aggregated.

Re: Bottom 5 values

Tue, 15 Apr 2014 07:53:30 GMT

try this?

=aggr(if(rank(sum({$<AccountName={'3010'}>} -Value))

<=5) ,Product),Product)

Re: Re: Bottom 5 values

Anonymous — Tue, 15 Apr 2014 08:03:45 GMT

Here's a test application

Re: Bottom 5 values

MK_QSL — Tue, 15 Apr 2014 08:58:17 GMT

Like this?

Calculated Dimension

=IF(Aggr(Rank(-SUM({<Account = {'3010'},Type = {'AC'}>}Value),4),Product)<=5,Product)

Expression

Total Value

SUM({<Account = {'3010'},Type = {'AC'}>}Value)

Rank

Rank(SUM({<Account = {'3010'},Type = {'AC'}>}Value),4)

Re: Bottom 5 values

nizamsha — Tue, 15 Apr 2014 09:08:19 GMT

Top 5 u can use this

if(rank(sum({$<AccountName={'3010'}>} Value))<=5,sum({$<AccountName={'3010'}>} Value)

bottom 5 u can use this

if(rank( rank(sum({$<AccountName={'3010'}>} Value)))<=5,sum({$<AccountName={'3010'}>} Value))

Re: Re: Re: Bottom 5 values

tresB — Tue, 15 Apr 2014 09:55:49 GMT

Use one more Aggr() like:

=aggr(if(Aggr(rank(-sum({$<Account={'3010'}, Type={'AC'}>} Value),4)<=5, Product),Product), Product)

Re: Bottom 5 values

arjunkrishnan — Tue, 15 Apr 2014 11:45:23 GMT

TRY THIS ONE

=aggr(if(rank(sum(Revenue))<=5 or rank(-sum(Revenue))<=5,Customer),Customer)

Re: Bottom 5 values

Anonymous — Thu, 24 Apr 2014 08:36:28 GMT

For some reson the expression now works with the negative sign in front of it.

But. I hav another problem conencted to this. When listing the bottom 5 values it only show 3 of them. And what I've discovered is that it only shows the ones that have a negative value.

This is the list of the bottom products:

Product1	-0,50%
Product3	-0,04%
Product5	-0,01%
Product2	0,00%
Product7	0,00%
Product6	0,00%
Product10	0,00%
Product9	0,01%
Product8	0,01%

How do i get it to list the other values?

//A.

Re: Bottom 5 values

MK_QSL — Thu, 24 Apr 2014 08:41:48 GMT

Have you tried my solution?

Re: Bottom 5 values

Anonymous — Thu, 24 Apr 2014 08:49:21 GMT

That is the solution i have used also but it doesn't fix my problem with the percentage values that are negative and positive. If that is the problem but that's my conclusion.

Re: Bottom 5 values

MK_QSL — Thu, 24 Apr 2014 08:52:12 GMT

If possible, please stick with your sample data provided earlier...

Now you are coming with Product1, Product2.... it's difficult to work...

Kindly provide correct sample data along with your required result... Would love to help you.

Re: Re: Bottom 5 values

Anonymous — Thu, 24 Apr 2014 09:19:20 GMT

Sorry about that, here's an extended version of sample data. I couldn't re-create the problem i had with the percentage values not showing all five. But instead the regulare value is not showing all bottom five but three of them only (the negatvie ones) so i'm thinking a solution to that might solve the real case i have aswell.

Re: Bottom 5 values

MK_QSL — Thu, 24 Apr 2014 09:22:39 GMT

If you check MRK349 file enclose on 15/04, you can see that my solution is working for Bottom 5 also.

Now, please let us know what is the percentage formula you are using?

Re: Bottom 5 values

Anonymous — Thu, 24 Apr 2014 09:26:31 GMT

Yes i can see that but the difference is the data i have in the new file. Where i have negative values so i think THAT is the problem.

Re: Bottom 5 values

MK_QSL — Thu, 24 Apr 2014 09:37:14 GMT

Can you provide -ve value data also?

Also, let us know how you are getting %

Re: Re: Bottom 5 values

Anonymous — Thu, 24 Apr 2014 10:12:48 GMT

Attached you'll find the source data.

How i get the procentage you can see in the expression.

Re: Bottom 5 values

MK_QSL — Thu, 24 Apr 2014 10:17:00 GMT

Sorry but where is the expression?