How to write sum expression for only top 10 products?

[#1]

Hi All

I'm displaying top 10 item_code having highest value for Obsolete Value Column using the calculated dimension:

=aggr(if(rank(sum({$<Item_category = {'$(=GetFieldSelections(Item_category))'}>}Obsolete_value))<=10,Item_code),Item_code) 

New requirement: How to display the sum of the Obsolete Value column = 8881.83 in a textbox?

I have tried the following but it's not working. Can you please assist me to display it?

='Total Obs Value for top 10 only ' & sum(aggr(if(rank(sum(total {$<Item_category = {'$(=GetFieldSelections(Item_category))'}>}Item_code))<=10,Item_code),Obsolete_value))

THanks in advance

[tresesco]

What is the expression you are using for your Obsolete value column in chart? Could you post your sample qvw?

[caio_caminoski]

Try:

='Total Obs Value for top 10 only ' & sum(aggr(if(rank(sum({$<Item_category = {'$(=GetFieldSelections(Item_category))'}>}Obsolete_value))<=10,Item_code),Item_code))

Probably you would need to format it as num as well make it look better.

Regards,

Caio

[#4]

Hi Tresesco,

I'm using below for Obsolete Value:

=sum({$<Item_category = {'$(=GetFieldSelections(Item_category))'}>}Obsolete_value)

[#5]

Hi Caio,

I've tried your expression but I'm getting sum = 0

[sunny_talwar · Accepted solution]

May be this

Sum({<Item_code = {"=Rank(Sum({$<Item_category = {'$(=GetFieldSelections(Item_category))'}>} Obsolete_value)) <= 10"}>} Obsolete_value)

You might be able to use the same expression for your straight table with just Item_code and Item_Description as dimensions

[#7]

Thanks Sunny This one works great!

[sunny_talwar]

One more thing, what is the use of this set analysis condition

Item_category = {'$(=GetFieldSelections(Item_category))'}

It seems to be suggesting that allow for selection in Item_category. QlikView will inherently do this. Unless I am missing something, this might work as well

Sum({<Item_code = {"=Rank(Sum(Obsolete_value)) <= 10"}>} Obsolete_value)

[caio_caminoski]

it is true, I am sorry, I thought you were applying the top 10 condition in your expression, not in your dimension.

[#10]

Thanks lots Sunny

You are right, after i removed the code Item_category = {'$(=GetFieldSelections(Item_category))'}, it is working good as well