topic Re: Pivot Table. Top3 Values in QlikView

Pivot Table. Top3 Values

Tue, 05 Jan 2016 11:49:30 GMT

Hi friends!

I am trying to show only 3 values per category:

Facturas
Recuperado

So what I tried was this: =if(aggr(rank(SUM(Datos)),[Origen Factura])<=3,[Origen Factura],Null())

But It didn´t work, it only shoy me the 3 values per one category ( facturas) due to this category is much higher/bigger than the other one but what I want is 3 values per category not 3 in total.

Many thanks

Re: Pivot Table. Top3 Values

Tue, 05 Jan 2016 14:32:03 GMT

Hi Juan ,

Do you want to see the result like the below pic

Re: Pivot Table. Top3 Values

Tue, 05 Jan 2016 14:49:03 GMT

Exactly!

How do you get it?

Re: Pivot Table. Top3 Values

Wed, 06 Jan 2016 06:09:50 GMT

Hi Juan ,

Use this expression in dimension if(aggr(rank(SUM(Datos)),[Facturas_Recuperado],[Origen Factura])<=3,[Origen Factura])

I attached qvw for your reference .

Re: Pivot Table. Top3 Values

tresB — Wed, 06 Jan 2016 06:15:44 GMT

Expression:

=if(aggr(rank(SUM(Datos)),Facturas_Recuperado,[Origen Factura])<=3,[Origen Factura],Null())

'Supress When Value is Null' - checked.

Re: Pivot Table. Top3 Values

Kushal_Chawda — Wed, 06 Jan 2016 06:18:26 GMT

create the calculated dimension as below

= aggr(if( rank(SUM(Datos),4)<=3,[Origen Factura]),Facturas_Recuperado,[Origen Factura])

check "Suppress when value is NULL"

Re: Pivot Table. Top3 Values

john9inno — Wed, 06 Jan 2016 06:21:51 GMT

does it have to be Pivot rather than Straight table?

I believe better design would be show top3 and sum of the rest as 'Others'

Re: Pivot Table. Top3 Values

jagan — Wed, 06 Jan 2016 06:22:17 GMT

Hi,

Try this expressions

Nº Envios

=If(Rank(SUM({<Envios_Gastos={'NºENVIOS'}>}Datos)) <=3, SUM({<Envios_Gastos={'NºENVIOS'}>}Datos))

Gastos

=If(Rank(SUM({<Envios_Gastos={'GASTOS'}>}Datos)) <=3, SUM({<Envios_Gastos={'GASTOS'}>}Datos))

Regards,

Jagan.

Re: Pivot Table. Top3 Values

tamilarasu — Wed, 06 Jan 2016 06:39:31 GMT

The same can be done by changing your expressions like below.

Nº Envios

SUM({<[Origen Factura]= {"=aggr(rank(SUM(Datos)),Facturas_Recuperado,[Origen Factura])<=3"},Envios_Gastos={'NºENVIOS'}>}Datos)

Gastos

SUM({<[Origen Factura]= {"=aggr(rank(SUM(Datos)),Facturas_Recuperado,[Origen Factura])<=3"},Envios_Gastos={'GASTOS'}>}Datos)

Re: Pivot Table. Top3 Values

Thu, 07 Jan 2016 10:44:15 GMT

It also works!!! Many thanks!

Re: Pivot Table. Top3 Values

Thu, 07 Jan 2016 10:44:36 GMT

It works!! I really apreciate it!

Re: Pivot Table. Top3 Values

Thu, 07 Jan 2016 10:53:58 GMT

The idea was to use pivot table, thanks for your response

Re: Pivot Table. Top3 Values

Thu, 07 Jan 2016 11:48:51 GMT

Hi Juan ,

Please close the thread as "Answered"

Thanks

Re: Pivot Table. Top3 Values

Kushal_Chawda — Thu, 07 Jan 2016 12:30:40 GMT

Like this?

see the attached

Re: Pivot Table. Top3 Values

Kushal_Chawda — Fri, 08 Jan 2016 06:20:54 GMT

Juan_Pedro86 Please close the thread by marking correct answer if you get what you are looking for