Problem with if condition

[geogou1973]

Hello.

Can anyone tell me how can i convert the following expression into set analysis because i have a lot of data and i have problem with the performance.

sum(if(FORMAT='FRANC' and SALES_DATE='$(PREVIOUS_UDATE)',AMOUNT_TRIG,

if(SALES_DATE='$(PREVIOUS_UDATE)',SALES_AMT_FPA)))

Thank you in advance.

[marcus_sommer]

For single-fileds calculations you could change it into:

sum({> FORMAT = {'FRANC'}, SALES_DATE = {'$(PREVIOUS_UDATE)'} >} AMOUNT_TRIG)

but this won't be applicable if you want to aggregate different fields depending on your conditions. But you could optimize the calculation by replacing the sum(if to if(sum, like:

if(FORMAT='FRANC' and SALES_DATE='$(PREVIOUS_UDATE), sum(AMOUNT_TRIG),

     if(SALES_DATE='$(PREVIOUS_UDATE)',sum(SALES_AMT_FPA))

Re: Sum(if(...)) vs If(Sum(...), Sum(...))

- Marcus

[Peter_Cammaert]

How about this?

sum({<FORMAT={'FRANC'}, SALES_DATE={'$(PREVIOUS_UDATE)'}>} AMOUNT_TRIG) +

sum({<FORMAT-={'FRANC'}, SALES_DATE={'$(PREVIOUS_UDATE)'}>} SALES_AMT_FPA)

Best,

Peter

PS. Marcus, do you ever sleep?

[marcus_sommer]

Not so well these days. I have caught a cold and after a coughing attack I'm awake  ... and then I think I could do anything useful ...

- Marcus

[Peter_Cammaert]

Well, hope you get well soon. Almost X-mas...