Rank problem In Pivot table with two dimensions

[paolojolly]

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

[jonathandienst]

Have you tried adding the TOTAL qualifier?

Rank(TOTAL .....)

[paolojolly]

Yes, I tried but it does not work

[shiveshsingh]

Hi

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?

[paolojolly]

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

[agomes1971]

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

[paolojolly]

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

[sunny_talwar · Accepted solution]

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

[paolojolly]

Thanks