Rank formula for selected store in the overall dataset

[Gavin_FBu]

Hi,

I have a raw data look like

Store	Category	Qty
A	Drink	1000
A	Food	500
A	Frozen	250
B	Drink	800
B	Food	400
B	Frozen	300
C	Drink	700
C	Food	600

In Qlik Sense dashboard, we have a page allow store user to select their own store and the table will shows the ranking for each category base on the store selected.  We try to create the formula for the Rank.  The challenge is while we selected a store in a filter, the whole data set filtered by that store.  We tried   TOTAL Rank([QTY])  or  put {[Store]=}  but it won't work.

eg:  Filter = Store A

        Table show:

Category	Rank
Drink	1
Food	2
Frozen	2

[vinieme12]

As below

=aggr({<Store=>}rank( sum({<Store=>}Qty),4,2),Category,Store)

[Gavin_FBu]

This is work in this make up model.  But while I apply into my real model (a different data set), its not.

In my model the Sales actually is a variable  eg:  $(TY_Sales) and this stopping the formula work (as it is working if I swap to Sales).  The variable is a formula to help me to only sum TY Sales as the data model contained 2 years of sales.  Do we have anyway to overcome this issue?

Also, can you educate me what is 4 , 2 means?  I still don't get it after read it from help.qlik.com

[vinieme12]

Can you paste the expression that you used in your app, so I can modify it for you

best way to understand different parameters is to try it yourself

[Gavin_FBu]

It looks like this

$(TY_Sales) =

if(Sum({$<zThisFYTD={"1"}>}Financial_Net_Sales)=0, null() ,Sum({$<zThisFYTD={"1"}>}Financial_Net_Sales))

(I got a flag call zThisFYTD to help identify this year)

Sorry, I am a beginner.... formula maynot be smart.

[vinieme12 · Accepted solution]

Just below should suffice,

$(TY_Sales) =  Sum({<zThisFYTD={'1'}>}Financial_Net_Sales)

Ranks = Aggr({<Store=>} Rank( Sum({<zThisFYTD={'1'}>}Financial_Net_Sales),4,1),Category,Store)

Under Add-Ons >> Data Handling >> Uncheck Include Zero Values

Also you must have the Store field as a Dimension in the chart

If not, you can just show overall rank by Category

Ranks = if(getselectedcount(Store)

,Aggr({<Store=>} Rank( Sum({<zThisFYTD={'1'}>}Financial_Net_Sales),4,1),Category,Store)

,Rank( Sum({<zThisFYTD={'1'}>}Financial_Net_Sales),4,1)

)

[Gavin_FBu]

It is working.  Thanks mate.