Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
geogou1973
Creator
Creator

Problem with if condition

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.

4 Replies
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
Partner - Champion III
Partner - Champion III

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
Partner - Champion III
Partner - Champion III

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