top 5 states

[sachin1]

I have columns for State, Share, and Sales, and I want to get the top 5 states where the Share is greater than zero, ranked by the highest Sales.

I want the Top 5 States as the dimension.

I am using below Logic:

IF(Aggr(Rank(SUM(Sales), 4), State) <= 5 AND SUM(Share) > 0, State)

State	Share	Sales	Top 5 State
Karnataka	-2	40
Kerala	-3	15
Madhya Pradesh	1	18	Madhya Pradesh
Maharashtra	5	30	Maharashtra
Manipur	2	12
Punjab	4	14
Rajasthan	8	19	Rajasthan
Tamil Nadu	9	17	Tamil Nadu
Telangana	6	16
Uttar Pradesh	7	24	Uttar Pradesh
West Bengal	3	13

[maxgro · Accepted solution]

try with 

=Aggr(
Only({$<State = {"=Rank(Sum({$<Share={[>0]}>} Sales))<=5"}>} State),
State)

[sachin1]

I am calculating the "Share" dynamically, this is not a field. how we can incorporate this calculation into set analysis.

Share Calculated Filed logic:  (Sum({<Product={'Apple'}>}Current_Year_Sales)
/
Sum(Total<State>Current_Year_Sales))
-
(Sum({<Product={'Apple'}>}Previous_Year_Sales)
/
Sum(Total<State>Previous_Year_Sales))

[Qrishna]

You can reference to the Calulcated measure into another measure. check this for more details:

https://community.qlik.com/t5/New-to-Qlik-Analytics/Referencing-Measures-in-other-Measures/td-p/1447...

[sachin1]

I have Define the "Share" as a Master Measure:

(Sum({<Product={'Apple'}>} Current_Year_Sales) / Sum(Total <State> Current_Year_Sales)) -
(Sum({<Product={'Apple'}>} Previous_Year_Sales) / Sum(Total <State> Previous_Year_Sales))

In the master, I have defined the logic and created the "Share" field as a measure. Then, I am using the "Share" field measure in the set analysis, as shown below.

Aggr(
Only({$<State = {"=Rank(Sum({$<Share={'>0'}>} Sales))<=5"}>} State),
State
)

But output is not getting.

[Qrishna]

Please look into the link i have attached: '- Unfortunately, Master Items cannot be referenced directly in other Master Items. This would be a nice feature, but it's not currently there.'

store various formulas in variables and then use the variable in the definition of the Master Items. Multiple variables can be combined in expressions. For example:

Variables:

      vexp_Sales = 'sum(Sales)'

      vexp_Cost = 'sum(Cost)'

Master Item Definitions:

    Total Sales = $(vexp_Sales)

    Total Cost = $(vexp_Cost)

    Margin % = ($(vexp_Sales) - $(vexp_Cost)) /$(vexp_Cost)

[sachin1]

I have tried this still not working.