Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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'))
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'))
Thank you very much, works perfectly!