topic Re: Re: Include 2 ranks in a 2 dimensions table in QlikView

Include 2 ranks in a 2 dimensions table

Mon, 09 Jun 2014 16:14:39 GMT

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

Re: Include 2 ranks in a 2 dimensions table

Gysbert_Wassenaar — Mon, 09 Jun 2014 16:23:19 GMT

See attached qvw.

Re: Include 2 ranks in a 2 dimensions table

Mon, 09 Jun 2014 16:26:16 GMT

Thanks a lot Gysbert ! Quick and efficient 🙂

Regards !

Re: Re: Include 2 ranks in a 2 dimensions table

Mon, 09 Jun 2014 17:10:57 GMT

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,

Re: Include 2 ranks in a 2 dimensions table

Gysbert_Wassenaar — Mon, 09 Jun 2014 17:19:23 GMT

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

Re: Include 2 ranks in a 2 dimensions table

Mon, 09 Jun 2014 17:34:01 GMT

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 !

Re: Re: Include 2 ranks in a 2 dimensions table

Mon, 09 Jun 2014 17:39:53 GMT

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

Re: Re: Re: Include 2 ranks in a 2 dimensions table

venkatg6759 — Mon, 09 Jun 2014 17:48:17 GMT

Check the attachment

Re: Re: Re: Include 2 ranks in a 2 dimensions table

Mon, 09 Jun 2014 17:53:15 GMT

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

😞

Re: Re: Include 2 ranks in a 2 dimensions table

Mon, 09 Jun 2014 18:22:00 GMT

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