topic Rank - List Top values and Group other values in QlikView

Rank - List Top values and Group other values

newqlik2017 — Wed, 25 Nov 2020 16:16:04 GMT

Looking for top 3 states:

1) If there are more than 3 states, all other states besides top 3 should be grouped into 'ALL OTHERS'

2) If there are 3 or less than 3 states, 'All OTHERS' should still be displayed but won't have any value

I used ranked function to create a calculated dimension but how do we create the result table as listed below.

Attached is a qvw with data for you reference. Any experts?

Below is the raw table and result table:

RAW TABLE
COUNTRY	STATE	AMOUNT
USA	CA	100
USA	TX	125
USA	FL	200
USA	PA	60
USA	NY	300
USA	NJ	200
CANADA	BC	150
CANADA	ON	75
CANADA	QC	50

RESULT TABLE
COUNTRY	1		2		3		OTHER
USA	NY	300	FL	200	NJ	200	ALL OTHERS	285
CANADA	BC	150	ON	75	QC	50	ALL OTHERS	-

Re: Rank - List Top values and Group other values

sunny_talwar — Tue, 31 Jul 2018 12:18:49 GMT

Try this

=Aggr(If(Rank(Sum(AMOUNT)) < 4, STATE, 'ALL OTHERS'), COUNTRY, STATE)

Re: Rank - List Top values and Group other values

newqlik2017 — Tue, 31 Jul 2018 12:32:59 GMT

Thanks Sunny. It's one step forward. I need to produce the final table as the Result table. While pivoting the state, the state is now a column but looking the states for each country to be a row and the corresponding values next to the state.

Any ideas how this can be done?

Re: Rank - List Top values and Group other values

sunny_talwar — Tue, 31 Jul 2018 12:42:21 GMT

Here you are

Dimensions

COUNTRY

=Aggr(If(Rank(Sum(AMOUNT)) < 4, Num(Rank(Sum(AMOUNT), 4)), 'OTHERS'), COUNTRY, STATE)

Expressions

=Aggr(If(Rank(Sum(AMOUNT)) < 4, STATE, 'ALL OTHERS'), COUNTRY, STATE)

SUM(AMOUNT)

Re: Rank - List Top values and Group other values

newqlik2017 — Tue, 31 Jul 2018 15:19:53 GMT

Thanks a lot!!!

Re: Rank - List Top values and Group other values

newqlik2017 — Tue, 31 Jul 2018 20:09:53 GMT

Hi Sunny, in this same example (QVW) if I need to populate 'ALL OTHERS' value for state always how can we achieve that?

'ALL OTHERS' text gets populated if there are more than 3 states. However, If there are 3 or less than 3 states (for instance CANADA that has 3 states) it's blank.

Re: Rank - List Top values and Group other values

sunny_talwar — Wed, 01 Aug 2018 02:04:00 GMT

Ya, I guess for non-existent stuff, it might be difficult to populate this...