ignore field is not working

[pgalvezt]

Hello,

I have 2 big requirement, one side... I have to show a pivot where I must Today()-1 sales... Today()-2 Sañes and Today()-3 Sales. For this in my script I have created 3 lines for Real - Budget and Last Year. Where my last field is 'one day' as Base concatenate with the same table but 'two days ago' as Base and so on...

So I have a field in my dashboard with  1 ... 2   and 3  Days.  This part works perfect...

The other side is obtain the total year. for this I have create the same Table (Clone) but historical... and concatenate with the first table... and changed the name of the calcutated fields...

All works fine except when select one of this days (1,2,3) plus another field that adjust the tax. (This field has Normal values - Adjust Values) works fine in Normal Values. But not in Adjust Values...

When deselec Field " Consulta Dias" the information is good in both cases (Normal Values -  Adjust Values)

Its seem in my code when I put ignore "Consulta Dias"  (Dont Work)

I attached the formula for better comprehenssion,

Thanks!!

[sunny_talwar · Accepted solution]

May be try this

=Num(

If(Only({<ConsultaDias>} xMarca) = 'Real' and Only({<ConsultaDias>} Pais) = 'Chile',

If(Only({<ConsultaDias>} xCod_Tipo) = 'Neto',

If(Only({<ConsultaDias>} vCurrency)='CLP',Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}ImporteH)*(UFmax_Chile) /xDivisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}Importe_TCH)*(UFmax_Chile) /xDivisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0')),

If(Only({<ConsultaDias>} vCurrency)='CLP',Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}Importe_IVAH)*(UFmax_Chile) /xDivisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}Importe_IVA_TCH)*(UFmax_Chile) /xDivisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0'))),

If(Only({<ConsultaDias>} xMarca) = 'Real' and Only({<ConsultaDias>} Pais) = 'Colombia',

If(Only({<ConsultaDias>} xCod_Tipo) = 'Neto',

If(Only({<ConsultaDias>} vCurrency)='CLP',Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}ImporteH)*(UFmax_Colombia) /xDivisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}Importe_TCH)*(UFmax_Colombia) /xDivisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0')),

If(Only({<ConsultaDias>} vCurrency)='CLP',Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}Importe_IVAH)*(UFmax_Colombia) /xDivisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}Importe_IVA_TCH)*(UFmax_Colombia) /xDivisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0'))),

If(Only({<ConsultaDias>} xMarca) = 'Real' and Only({<ConsultaDias>} Pais) = 'Perú',

If(Only({<ConsultaDias>} xCod_Tipo) = 'Neto',

If(Only({<ConsultaDias>} vCurrency)='CLP',Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}ImporteH)*(UFmax_Peru) /xDivisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}Importe_TCH)*(UFmax_Peru) /xDivisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0')),

If(Only({<ConsultaDias>} vCurrency)='CLP',Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}Importe_IVAH)*(UFmax_Peru) /xDivisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}Importe_IVA_TCH)*(UFmax_Peru) /xDivisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0'))),

If(Only({<ConsultaDias>} xMarca) = 'Real' and Only({<ConsultaDias>} Pais) = 'México',

If(Only({<ConsultaDias>} xCod_Tipo) = 'Neto',

If(Only({<ConsultaDias>} vCurrency)='CLP',Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}ImporteH)*(UFmax_Mexico) /xDivisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}Importe_TCH)*(UFmax_Mexico) /xDivisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0')),

If(Only({<ConsultaDias>} vCurrency)='CLP',Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}Importe_IVAH)*(UFmax_Mexico) /xDivisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}Importe_IVA_TCH)*(UFmax_Mexico) /xDivisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0'))),

If(Only({<ConsultaDias>} xCod_Tipo) = 'Neto',

If(Only({<ConsultaDias>} vCurrency)='CLP',Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}ImporteH)*(UFmax) /xDivisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}Importe_TCH)*(UFmax) /xDivisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0')),

If(Only({<ConsultaDias>} vCurrency)='CLP',Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}Importe_IVAH)*(UFmax) /xDivisor,'$ #.##0;-$ #.##0'),

Num(Num(Sum({$<Año={$(=Year(Today()))},Base={'Historico'},ConsultaDias =>}Importe_IVA_TCH)*(UFmax) /xDivisor,'$ #.##0;-$ #.##0'),'US$ #.##0;-US$ #.##0'))))))),

If(Only({<ConsultaDias>} vCurrency)='CLP', '$ #.##0;-$ #.##0', 'US$ #.##0;-US$ #.##0'))

[pgalvezt]

Hi Sunny,

Thank you for your reply,

I changed the variables for the original fields and works!!!

Thanks a lot...