Total Problem (Pivot Table)

[pgalvezt]

Hello,

I have a problem when I change the filter selection from 'A' to 'B', I tried to change the aggregation formula but until now it doesn´t work,

I attached a little example. All what I want is the real sum when the selection is 'B'. 'A' Works fine.

Thanks!

[sunny_talwar · Accepted solution]

Can you check if this works

=Num(

Sum(Aggr(If(MarcaUF = 'B' and Pais = 'Chile',

If(Cod_Tipo_Monto = 'Bruto',

If(vCurrency='CLP',Num(Sum(Importe)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)},Pais={'Chile'}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum(Importe_TC)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)},Pais={'Chile'}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0')),

If(vCurrency='CLP',Num(Sum(Importe_IVA)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)},Pais={'Chile'}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum(Importe_IVA_TC)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)},Pais={'Chile'}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0'))),

If(MarcaUF = 'B' and Pais = 'Colombia',

If(Cod_Tipo_Monto = 'Bruto',

If(vCurrency='CLP',Num(Sum(Importe)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)},Pais={'Colombia'}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum(Importe_TC)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)},Pais={'Colombia'}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0')),

If(vCurrency='CLP',Num(Sum(Importe_IVA)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)},Pais={'Colombia'}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum(Importe_IVA_TC)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)},Pais={'Colombia'}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0'))),

If(MarcaUF = 'B' and Pais = 'México',

If(Cod_Tipo_Monto = 'Bruto',

If(vCurrency='CLP',Num(Sum(Importe)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)},Pais={'México'}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum(Importe_TC)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)},Pais={'México'}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0')),

If(vCurrency='CLP',Num(Sum(Importe_IVA)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)},Pais={'México'}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum(Importe_IVA_TC)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)},Pais={'México'}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0'))),

If(MarcaUF = 'B' and Pais = 'Perú',

If(Cod_Tipo_Monto = 'Bruto',

If(vCurrency='CLP',Num(Sum(Importe)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)},Pais={'Perú'}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum(Importe_TC)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)},Pais={'Perú'}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0')),

If(vCurrency='CLP',Num(Sum(Importe_IVA)*Aggr(sum({<Mes={$(xaMesMax)},Año={$(xAñoMax)},Pais={'Perú'}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum(Importe_IVA_TC)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)},Pais={'Perú'}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0'))),

If(Cod_Tipo_Monto = 'Bruto',

If(vCurrency='CLP',Num(Sum(Importe)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum(Importe_TC)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0')),

If(vCurrency='CLP',Num(Sum(Importe_IVA)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum(Importe_IVA_TC)*Aggr(sum({<Mes={$(xMesMax)},Año={$(xAñoMax)}>} Distinct Total ValorUF),Local2) /Divisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0'))))))), Pais)),

If(GetFieldSelections(Seleccionar_Currency) = 'CLP', '$ #.##0;-$ #.##0', 'US$ #.##0;-US$ #.##0'))

[pgalvezt]

Thank you very much, works perfectly!