topic Re: Row Number for Pivot Table in QlikView

Row Number for Pivot Table

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

Hello,

I have a Pivot Table with the following data:

- Group/Customer are Dimensions

- Total is the Expression

Each Group can contain 1 or more Customers.

What I need is to create a Ranking with numbers from 1 to N (sorted by Total expression):

And then if I expand Group, I should see something like this (keeping the Ranking number for each Group):

Is there any way to do that on my chart?

Thank you!

YoussefBelloum — Tue, 21 Nov 2017 17:03:48 GMT

Hi,

Try this on the ranking expression:

=aggr(NODISTINCT rank(sum(total),Group),Group)

devarasu07 — Tue, 21 Nov 2017 17:07:35 GMT

Hi,

may be this?

Rank:

Aggr( NODISTINCT Rank(Sum( Total)),Group)

Total:

sum(Total)

microwin88x — Tue, 21 Nov 2017 17:37:35 GMT

Thank you! It worked. The only thing is:

I have excluded Group = H1 like this: IF(Group<>'H1',Group) and then I've hidden Nulls.

When I have my Rank expression, it starts from 2, 3, 4, 5... (Because 1 would be H1)...

Is there any way to force it to start from 1?

Thanks again!

microwin88x — Tue, 21 Nov 2017 17:37:48 GMT

Thank you! It worked. The only thing is:

I have excluded Group = H1 like this: IF(Group<>'H1',Group) and then I've hidden Nulls.

When I have my Rank expression, it starts from 2, 3, 4, 5... (Because 1 would be H1)...

Is there any way to force it to start from 1?

Thanks again!

antoniotiman — Tue, 21 Nov 2017 17:51:44 GMT

In this case Expressions :

Sum({<Group-={'H1'}>} Value)

and

Aggr(NODISTINCT Rank(TOTAL Sum({<Group-={'H1'}>} Value)),Group)