topic Re: Rank of dimension not used in chart in QlikView

Rank of dimension not used in chart

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

Hi,

I am new to QlikView and need some help.

I have a table of

Category 1	Category 2
A	A-1
A	A-2
A	A-3
B	B-1
B	B-2
C	C-1
C	C-2
C	C-2
C	C-3
C	C-4

For each item of category 2, I want to add a new field what represents the rank of count of Category 1:

Category 1	Category 2	Rank of Count Category 1
A	A-1	2
A	A-2	2
A	A-3	2
B	B-1	3
B	B-2	3
C	C-1	1
C	C-2	1
C	C-2	1
C	C-3	1
C	C-4	1

So based on the new field, I want to create a pie chart which only shows the count of category 2 of which the rank of count of category 1 is "2".

Any way to do it? Thanks!

Re: Rank of dimension not used in chart

sunny_talwar — Mon, 20 Aug 2018 11:29:34 GMT

You can create your new field like this

Table:

LOAD * INLINE [

Category 1, Category 2

A, A-1

A, A-2

A, A-3

B, B-1

B, B-2

C, C-1

C, C-2

C, C-3

C, C-4

];

Left Join (Table)

LOAD [Category 1],

Count(DISTINCT [Category 2]) as Count

Resident Table

Group By [Category 1];

FinalTable:

LOAD *,

If([Category 1] <> Previous([Category 1]), RangeSum(Peek('Rank of Count Category 1'), 1), Peek('Rank of Count Category 1')) as [Rank of Count Category 1]

Resident Table

Order By Count desc;

DROP Table Table;

and then use this in field in set analysis like this

=Count({<[Rank of Count Category 1] = {2}>}[Category 2])

Re: Rank of dimension not used in chart

Anonymous — Tue, 21 Aug 2018 00:56:12 GMT

Thanks for the reply!!

Sorry that I am new to QlikView, I am able generate a straight table like what you shown. However, how to plot the pie chart with only one dimension? I tried to create a pie chart with Dimension "Category 2" and use "Count({<[Rank of Count Category 1] = {2}>}[Category 2])" as expression, it doesn't give the correct ranking. Any idea what I did wrongly? thanks!!

Re: Rank of dimension not used in chart

sunny_talwar — Tue, 21 Aug 2018 11:05:59 GMT

Are you looking for the chart like the image above? Does that one look right? If not, can you point out what isn't right?