Include 2 ranks in a 2 dimensions table

[#1]

Hi Q-Community,

I’m experiencing few problems trying to show two different rankings in the same table. The first ranking I want to show is on the value “Margen Explotación”, and I’d like to make a “global” order (first dimension = ID_BRANCH). Here, I mean I’d like to compare all the branches.

The second ranking would be a ranking by group (second dimension = ID_CLUSTER).

To be more concrete, as an example, I’d like to see the branch 52 is the 9^th in terms of “Margen Explotación” (according to the first dimension), and the 3^rd of his Cluster (according to the second dimension).

Thanks a lot for your help guys!

Kind regards,

Vincent

[Gysbert_Wassenaar]

See attached qvw.

[#3]

Thanks a lot Gysbert ! Quick and efficient 🙂

Regards !

[#4]

Uuupps, sorry, I have another problem. I experiment the same problem again because instead of a field, [Margen Explotación] is the calculation of two fields. Do you know how to take in consideration this case also? See new version of the document you sent.

Thanks a lot for your help and sorry for inconveniences.

Regards,

[Gysbert_Wassenaar]

Replace [Margen Explotación] with rangesum(Ingresos,-Gastos)

[#6]

I've tried with sum(aggr(rank(rangesum(Ingresos,-Gastos)),ID_BRANCH)), and it's OK. However, the second one does not give me the expected result:

- sum(aggr(NODISTINCT rank(total sum(rangesum(Ingresos,-Gastos))),CÓDIGO_CLUSTER)), >>>  it shows me the results from 1 to 8

- sum(aggr(NODISTINCT rank(total (rangesum(Ingresos,-Gastos))),CÓDIGO_CLUSTER)) >>>  show always 4,5

Do you know where I'm failing with the expression? Thanks a lot !

[#7]

A very very last question ! If I rename Ingresos expression "ABC" and Gastos expression "DEF", and calculate Margen Explotación = ABC-DEF, neither the first ranking expression works. Would you have a solution in that case also?

Thanks a lot again.

Regards,

Vincent

[venkatg6759]

Check the attachment

[#9]

Hi venkatg6759,

Thanks for your help, but in that case it doesn't work as expected. Using ABC and DEF instead of [Margen Explotación], results are 54,5 and 4,5

😞

[#10]

Hivenkatg6759,

As an answer to your question I received in my inbox, yes, there's a reason I try to use the label. The label in my original file is calculated with the following formula:

- INGRESOS = SUM({<TIPO_GASTO={'INGRESOS'}, CONTADORMES_GASTO={$(vMaxContador)}, AÑO_GASTO=>}IMPORTE_GASTO)

- GASTOS = SUM({<TIPO_GASTO=-{'INGRESOS'}, CONTADORMES_GASTO={$(vMaxContador)}, AÑO_GASTO=>}IMPORTE_GASTO)

But, when using both of the formula, it doesn't take in consideration the  GASTOS formula and range the branches only by INGRESOS.

RANK(RANGESUM(SUM({<TIPO_GASTO={'INGRESOS'}, CONTADORMES_GASTO={$(vMaxContador)}, AÑO_GASTO=>}IMPORTE_GASTO)),-(SUM({<TIPO_GASTO=-{'INGRESOS'}, CONTADORMES_GASTO={$(vMaxContador)}, AÑO_GASTO=>}IMPORTE_GASTO)))