Rank of dimension not used in chart

[Anonymous]

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!

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

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?