topic Re: Rank problem In Pivot table with two dimensions in QlikView

Rank problem In Pivot table with two dimensions

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

Hello all,

I am trying to create a pivot table with two dimensions(CLIENTE and AGENTE) in which i need to show for each CLIENTE/AGENTE the sum of W_TOT_IMP_RIGA_NETNET and the rank values of W_TOT_IMP_RIGA_NETNET for CLIENTE dimension.

I would like to get a table like the following

possibly ordered for the rank value, instead I get

I do not understand where I'm wrong. can someone help me? Thank you

Re: Rank problem In Pivot table with two dimensions

jonathandienst — Thu, 07 Jun 2018 09:18:24 GMT

Have you tried adding the TOTAL qualifier?

Rank(TOTAL .....)

Re: Rank problem In Pivot table with two dimensions

paolojolly — Thu, 07 Jun 2018 09:22:35 GMT

Yes, I tried but it does not work

Re: Rank problem In Pivot table with two dimensions

shiveshsingh — Thu, 07 Jun 2018 09:41:02 GMT

As per our data, you should get 14 ranks if we look on the combination of D_Cliente and D_Agente. What's the logic of getting only 6 ranks?

Re: Rank problem In Pivot table with two dimensions

paolojolly — Thu, 07 Jun 2018 09:48:54 GMT

sales area colleagues do not want to see records with sum of W_TOT_IMP_RIGA_NETNET at zero

Re: Rank problem In Pivot table with two dimensions

agomes1971 — Thu, 07 Jun 2018 10:01:47 GMT

Hi,

I get this:

CLIENTE	AGENTE	=Rank(sum(W_TOT_IMP_RIGA_NETNET),CLIENTE)	sum(W_TOT_IMP_RIGA_NETNET)
A.S. SRL	SG SPA	1	€746.84
A.S. SRL	T.M.	2	€2.80
A.S. SRL	F.S.	3	€1.08
A.S. SRL	R.P.	4	€0.11
AAA SPA	SG SPA	1	€1137.41
LLL SPA	SG SPA	1	€832.84
LLL SPA	T.M.	2	€18.92
LLL SPA	F.S.	3	€17.11
P.P.SPA	SG SPA	1	€188.51
S.RI. SPA	S.R. AA	1	€232.10
SSS SPA	SG SPA	1	€196.09
SSS SPA	T.M.	2	€13.60
SSS SPA	F.S.	3	€8.19
SSS SPA	R.P.	4	€0.13

HTH

André Gomes

Re: Rank problem In Pivot table with two dimensions

paolojolly — Thu, 07 Jun 2018 10:12:30 GMT

in yuor solution rank value is not related to CLIENTE but to CLIENTE/AGENTE, in addition W_TOT_IMP_RIGA_NETNET should be filtered on dates(

if(DATA_RIF_DOC >= vDataIniCorr and DATA_RIF_DOC <= vDataFinCorr ,

W_TOT_IMP_RIGA_NETNET

)

thanks

Re: Rank problem In Pivot table with two dimensions

sunny_talwar — Thu, 07 Jun 2018 10:18:31 GMT

Try this

If(Column(2) <> 0, Aggr(NODISTINCT rank( Sum({<D_AGENTE_DEST=>}

if(DATA_RIF_DOC >= vDataIniCorr and DATA_RIF_DOC <= vDataFinCorr AND W_TOT_IMP_RIGA_NETNET <> 0,

W_TOT_IMP_RIGA_NETNET

)

)),D_CLIENTE))

Re: Rank problem In Pivot table with two dimensions

paolojolly — Thu, 07 Jun 2018 12:37:11 GMT

Thanks