topic Last 10 Items! in QlikView

Last 10 Items!

Wed, 29 Sep 2010 09:45:08 GMT

Hi All,

Using the below I used to find the Top 10 ranking items along with this FSName is also there in the dimension field which is used to display the FSName. In the expression field I used Sum(Value) to show the value of the Top 10 items.

Same thing I want to do for the Last 10 items. Don't know exactly what changes I have to do in the below script. Can some one please help me out to do the same.


=IF(
aggr(
rank(total
aggr( sum(Value),FSName)
,0,1)
,FSName)
<=10,

aggr(
rank(total
aggr( sum(Value),FSName)
,0,1)
,
FSName))

Last 10 Items!

johnw — Thu, 30 Sep 2010 02:21:28 GMT

Just change the sign: -sum(Value)

Last 10 Items!

Thu, 30 Sep 2010 04:53:05 GMT

Hi John,

I already tried with that. In that case it shows only the -ve value. It is not showing up the positive values. In my application sales value contain both +ve as well as -ve values.

Have attached the actual and expected result below.

Actual:

Expected:

Last 10 Items!

Thu, 30 Sep 2010 05:25:57 GMT

Hi John,

What is the use of -ve sign before Sum(Value). First of all I thought it sort the value from lowest to highest. Later when checked realized that it doesn't do that. So could you please for what purpose we are putting -ve sign before Sum(Value).

Thanks in advance

Last 10 Items!

johnw — Thu, 30 Sep 2010 17:19:11 GMT

Please post an example with the problem, then. As should be obvious, flipping the sign will reverse the rankings. And unless there's a bug I'm not aware of, reversing the ranking by flipping the sign should not prevent positive values from showing.

Last 10 Items!

Fri, 01 Oct 2010 05:50:16 GMT

Hi John,

Thanks for explaining me the use of -ve sign before sum. By the way as requested I have attached the document for you to work with. Hope the issue will be understandable now.

Last 10 Items!

jonathandienst — Fri, 01 Oct 2010 08:00:26 GMT

Rikab

If you turn off the suppress zero value option in the Presentation tab of your Last 10 FS's and your Sales Value for Reference tables, you will see that there are a number of FSNames with value of zero. These all tie for 3rd place when ranking the bottom 10.

That explains why you only see two when zeroes are suppressed.

Jonathan