topic Re: Need help bringng Rank into Straight table in QlikView

Need help bringng Rank into Straight table

jagdishbr — Sat, 16 Nov 2024 00:50:20 GMT

Hi,

I'm new to qlikview and have requirement to bring rank into straight table and below are available columns

the requirement is to calculate rank by considering only 3 column Region, Product and Period.

Client ID	Region	Product	Period	Sales	Rank
3928	APAC	CR	FY18	6131
4888	APAC	CR	FY18	5083
1026	APAC	CR	FY18	3524
3928	APAC	CR	1H19	6491
4888	APAC	CR	1H19	4661
1026	APAC	CR	1H19	9078
3928	Americas	CR	FY18	3743
4888	Americas	CR	FY18	7832
1026	Americas	CR	FY18	7469
3928	Americas	CR	1H19	8104
4888	Americas	CR	1H19	8620
1026	Americas	CR	1H19	6767
3928	APAC	AR	FY18	2178
4888	APAC	AR	FY18	1827
1026	APAC	AR	FY18	7328
3928	APAC	AR	1H19	6117
4888	APAC	AR	1H19	6399
1026	APAC	AR	1H19	3358
3928	Americas	AR	FY18	4105
4888	Americas	AR	FY18	7813
1026	Americas	AR	FY18	6788
3928	Americas	AR	1H19	7567
4888	Americas	AR	1H19	4463
1026	Americas	AR	1H19	5986

Please help me.

Re: Need help bringng Rank into Straight table

sunny_talwar — Wed, 15 Apr 2020 19:50:50 GMT

What is the expected output for the rank column based on the sample data shared?

Re: Need help bringng Rank into Straight table

sunny_talwar — Wed, 15 Apr 2020 19:55:29 GMT

May be this

Aggr(Rank(Sum(Sales), Region, Product, Period, [Client ID])

This will give you rank for each Client ID within a single Region, Product, Period. Rank will reset to 1 as any of the three dimension value changes.

Re: Need help bringng Rank into Straight table

jagdishbr — Wed, 15 Apr 2020 20:16:08 GMT

Thanks for your response sunny.

If we include client Id as a parameter, rank will get populated for client for each products.

The requirement is to get top 25 clients for each products by region by period

If I'm not clear, I will share the table agin populating rank.

Re: Need help bringng Rank into Straight table

jagdishbr — Wed, 15 Apr 2020 20:48:20 GMT

This is the output expected.

Client ID	Region	Product	Period	Sales	Rank
3928	APAC	CR	FY18	6131	1
4888	APAC	CR	FY18	5083	2
1026	APAC	CR	FY18	3524	3
3928	APAC	CR	1H19	6491	2
4888	APAC	CR	1H19	4661	3
1026	APAC	CR	1H19	9078	1
3928	Americas	CR	FY18	3743	3
4888	Americas	CR	FY18	7832	1
1026	Americas	CR	FY18	7469	2
3928	Americas	CR	1H19	8104	2
4888	Americas	CR	1H19	8620	1
1026	Americas	CR	1H19	6767	3
3928	APAC	AR	FY18	2178	2
4888	APAC	AR	FY18	1827	3
1026	APAC	AR	FY18	7328	1
3928	APAC	AR	1H19	6117	2
4888	APAC	AR	1H19	6399	1
1026	APAC	AR	1H19	3358	3
3928	Americas	AR	FY18	4105	3
4888	Americas	AR	FY18	7813	1
1026	Americas	AR	FY18	6788	2
3928	Americas	AR	1H19	7567	1
4888	Americas	AR	1H19	4463	2
1026	Americas	AR	1H19	5986	2

Re: Need help bringng Rank into Straight table

sunny_talwar — Thu, 16 Apr 2020 13:26:58 GMT

I had a typo in my expression above.... try this

Aggr(Rank(Sum(Sales)), Region, Product, Period, [Client ID])

Re: Need help bringng Rank into Straight table

jagdishbr — Thu, 16 Apr 2020 18:21:19 GMT

Thanks for your help sunny,

I could see the output as required. however, there are clients without sales or as sales as 0 for those client rank started appearing with in the range like 1213-1339. in ran column is there a way to fix this?

it will be better Rank continues as 1213, 1214,1215 till 1339 or appear 1213 rest of clients whose sales = 0

Re: Need help bringng Rank into Straight table

jagdishbr — Thu, 16 Apr 2020 18:51:56 GMT

I figured out the solution by enhancing your function by value to mode and fmt.

Aggr(Rank(Sum(Sales), 4, 1), Region, Product, Period, [Client ID])