Pivot Table. Top3 Values

[#1]

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

[#2]

Hi Juan ,

Do you want to see the result like the below pic

[#3]

Exactly!

How do you get it?

[#4]

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 .

[tresesco]

Expression:

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

'Supress When Value is Null' - checked.

[Kushal_Chawda]

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"

[john9inno]

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'

[jagan]

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.

[tamilarasu]

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)

[#10]

It also works!!! Many thanks!