topic Re: Generate Ranks Per Month in QlikView

Generate Ranks Per Month

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

Hello All,

I have my data as in the attached Excel.

Basically 4 Columns (An overview of how my data is structured):

Area Code	Month	Market	Value
XX	1	AA	20

Please, check the Excel Table for more details.

From, this data i'm looking to generate Rank for Area based on the Value.

Ex:

In Jan Area 1009 is Rank1, 1001 is 2 ......1004 is 10

In Feb Area 1010 is Rank1.....1005 is Rank10

I have used the RecNo() function to generate the Ranks in Script, but i'm not able to generate Ranks per Month.

Ex: For the month of Feb with RecNo() i was able to generate the following ranks:

But, what i'm looking for is Rank 1-10 per month.

TIA!!

Re: Generate Ranks Per Month

sunny_talwar — Tue, 26 Jun 2018 14:44:37 GMT

Try this

Raw_Data:

LOAD Distinct *

FROM

[Rank_Test.xlsx]

(ooxml, embedded labels, table is Tabelle1);

Ranking:

LOAD *,

If(Month = Previous(Month), RangeSum(Peek('Ranking'), 1), 1) as Ranking,

AutoNumber(RecNo(), Month) as Ranking2

Resident Raw_Data

Order by Month, Value desc;

DROP Table Raw_Data;

I will go with the green option... but I have added the blue one too...

Re: Generate Ranks Per Month

psk180590 — Thu, 28 Jun 2018 13:05:49 GMT

Hello Sunny,

Your solution has perfectly worked for me thanks for this.

But, now i need to create Regiob from Area Code and also generate Ranks for the Regions. I'm somehow not able to acheive it through both the logics you defined.

Please, have a look at the updated qvd and Excel Table.

Thanks Again and Best Regards

Sai.

Re: Generate Ranks Per Month

sunny_talwar — Thu, 28 Jun 2018 13:12:35 GMT

What is the expected output?

Re: Generate Ranks Per Month

psk180590 — Thu, 28 Jun 2018 13:14:22 GMT

The 'RankingReg' should always have 8(1-8 Ranks).

Same logic as Area Code(Which has 1-91 Ranks always)

Re: Generate Ranks Per Month

sunny_talwar — Thu, 28 Jun 2018 13:30:08 GMT

Can you check if attached is what you want

Re: Generate Ranks Per Month

psk180590 — Thu, 28 Jun 2018 13:34:01 GMT

Many Thanks looks good. I will give it a try right away!!

Re: Generate Ranks Per Month

sunny_talwar — Thu, 28 Jun 2018 13:36:12 GMT

Sounds good