Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Rank problem In Pivot table with two dimensions

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



si req 07.06.2018.png

possibly ordered for the rank value, instead I get

no req 07.06.2018.png


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

1 Solution

Accepted Solutions
Highlighted

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

Capture.PNG

View solution in original post

8 Replies
Highlighted

Have you tried adding the TOTAL qualifier?

Rank(TOTAL .....)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Creator
Creator

Yes, I tried but it does not work

Highlighted
Master
Master

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?

Highlighted
Creator
Creator

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

Highlighted
Specialist II
Specialist II

Hi,

I get this:

CLIENTE AGENTE =Rank(sum(W_TOT_IMP_RIGA_NETNET),CLIENTE) sum(W_TOT_IMP_RIGA_NETNET)
A.S. SRLSG SPA1€746.84
A.S. SRLT.M.2€2.80
A.S. SRLF.S.3€1.08
A.S. SRLR.P.4€0.11
AAA SPASG SPA1€1137.41
LLL SPASG SPA1€832.84
LLL SPAT.M.2€18.92
LLL SPAF.S.3€17.11
P.P.SPASG SPA1€188.51
S.RI. SPAS.R. AA1€232.10
SSS SPASG SPA1€196.09
SSS SPAT.M.2€13.60
SSS SPAF.S.3€8.19
SSS SPAR.P.4€0.13

HTH

André Gomes

Highlighted
Creator
Creator

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

Highlighted

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

Capture.PNG

View solution in original post

Highlighted
Creator
Creator

Thanks