topic Re: Re: Rank in pivot table in QlikView

Rank in pivot table

Tue, 07 Oct 2014 13:28:14 GMT

Hi everybody,

I have a pivot table with 2 dimensions, Quarter and Area and some expressions.

This pivot table is shown only when the user select only one company because there are some expressions about the market (= all companies) and some others about the selected company.

My pivot table is like this (dimensions in bold and expressions in italic)

Area Q1 Q2 Q3 Q4

North America

Sales Market 100 120 110 140

Sales Selected Company 30 40 25 50

Rank Company 2 2 2 2

South America

Sales Market 110 125 130 140

Sales Selected Company 35 30 20 40

Rank Company 1 1 1 1

Among my expressions, I need to calculate the rank but if I use this expression : =rank(sum({<$(CTXT_COMPANY),$(CTXT_REBATES)>}SALES)), the rank calculated is 1 or 2 because it’s calculated for only the selected company among Area.

I would like calculate the rank of the selected company among all companies, by area.

Could you help me for this ?

Thank you

Regards

Bérengère

Re: Rank in pivot table

Gysbert_Wassenaar — Tue, 07 Oct 2014 13:33:38 GMT

Try: rank(TOTAL sum({<$(CTXT_COMPANY),$(CTXT_REBATES)>}SALES))

Re: Rank in pivot table

Tue, 07 Oct 2014 13:41:03 GMT

Hello Gysbert,

Thank you for your response. Unfortunately, I tried this and the result is the same.

Regards

Bérengère

Re: Rank in pivot table

Gysbert_Wassenaar — Tue, 07 Oct 2014 13:53:04 GMT

Can you post an example document that demonstrates the problem?

Re: Re: Rank in pivot table

Tue, 07 Oct 2014 14:13:07 GMT

Here is an example of my problem.

Thank you

Regards

Bérengère

Re: Rank in pivot table

Gysbert_Wassenaar — Tue, 07 Oct 2014 14:22:57 GMT

Well, you can show the correct numbers with =rank(TOTAL sum({<Company>}Sales)). But then you will get all companies in the chart even if you select only one company in the list box.

Re: Re: Rank in pivot table

Tue, 07 Oct 2014 14:29:09 GMT

Yes but as you said, I get all companies in the pivot table and not only the selected company...

Re: Rank in pivot table

Clever_Anjos — Tue, 07 Oct 2014 14:34:31 GMT

Try this:

=if(Column(1) > 0,rank(TOTAL sum({<Company=>}Sales)))

Re: Re: Rank in pivot table

Tue, 07 Oct 2014 14:44:27 GMT

Hello Clever,

Thank you for your response. Results of expressions for others companies are 0 or null but they still appear in the pivot table.

Regards

Bérengère

Re: Re: Rank in pivot table

Clever_Anjos — Tue, 07 Oct 2014 17:13:35 GMT

Weird... It worked here

Re: Re: Re: Rank in pivot table

Clever_Anjos — Tue, 07 Oct 2014 17:23:56 GMT

Please, find attached

Re: Re: Rank in pivot table

Wed, 08 Oct 2014 07:31:25 GMT

Thank you for your help Clever, I don't understand why it doesn't work with my file...

I have this result.

Regards

Bérengère

Re: Rank in pivot table

Clever_Anjos — Wed, 08 Oct 2014 12:45:22 GMT

Please check if "Supress zero values" is checked under Presentation Tab

Re: Rank in pivot table

Wed, 08 Oct 2014 12:49:03 GMT

Yes, it's checked in Presentation tab and in Dimension tab

Regards

Bérengère

Re: Rank in pivot table

Clever_Anjos — Wed, 08 Oct 2014 13:04:03 GMT

When you open my qvw is not working?

Which version are you using?

Re: Rank in pivot table

Wed, 08 Oct 2014 13:11:31 GMT

Even with your qvw, it doesn't work. I use 11.2 version. Is-it the same as yours ?

Re: Re: Rank in pivot table

Clever_Anjos — Wed, 08 Oct 2014 13:17:07 GMT

Yes...

11.20

Please see how it works when "Toto" is selectect or not

Re: Rank in pivot table

rubenmarin — Wed, 08 Oct 2014 13:25:42 GMT

Mine is 11.2 SR6 and Clever's solution works fine.

Re: Rank in pivot table

Wed, 08 Oct 2014 13:28:51 GMT

Clever,

Your version is 11.2 SR6 like Ruben ? Because I use 11.2 SR3

Re: Rank in pivot table

Clever_Anjos — Wed, 08 Oct 2014 13:45:11 GMT

11.20 SR8