topic Re: top 5 states in App Development

top 5 states

sachin1 — Fri, 15 Nov 2024 20:50:59 GMT

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

Re: top 5 states

maxgro — Wed, 23 Oct 2024 08:50:39 GMT

try with

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

Re: top 5 states

sachin1 — Thu, 24 Oct 2024 06:30:36 GMT

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))

Re: top 5 states

Qrishna — Fri, 25 Oct 2024 04:46:05 GMT

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/1447667

Re: top 5 states

sachin1 — Fri, 25 Oct 2024 06:30:19 GMT

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.

Re: top 5 states

Qrishna — Fri, 25 Oct 2024 22:22:20 GMT

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)

Re: top 5 states

sachin1 — Mon, 28 Oct 2024 06:05:06 GMT

I have tried this still not working.