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: 
nkosinde13
Creator
Creator

If statement with where clause in expression

Good day Community

I have an expression below, which works perfectly.

=IF(Num(Sum({<$(eSetFYTD_CN)>}Amount) / $(ThouMenu),'# ##0')< 0 , '100%-')

So I want to add an additional condition where the  Dim Category = Costs , for  example. How do I then refine the expression to include the condition? 

Thanks and regards

Matlotlo

 

 

 

5 Replies
lironbaram
Partner - Master III
Partner - Master III

hi 

you have two options : 

1. use if statement 
     =IF(Num(Sum({<$(eSetFYTD_CN) >}if(Dim Category = Costs,Amount)) / $(ThouMenu),'# ##0')< 0 , '100%-')

    pros :
    this expression is completely effected from selections made in the app 

    cons:

    performance wise it's might work slower than solution 2 as it been evaluated  for each row 

2. use set analysis 

         =IF(Num(Sum({<$(eSetFYTD_CN),Dim Category = {"Costs"}>}Amount) / $(ThouMenu),'# ##0')< 0 , '100%-')

     pros:
      performance will be better as it's replicate a selection at model level 

     cons :
    selection made in the dim category field won't effect the calculation and might confuse the user

nkosinde13
Creator
Creator
Author

@lironbaram  Thank you very much for your quick assistance. The set analysis option is perfect. I do not want it to be affected by any selection since I';m just assigning '100%-' to all values that meet the conditions in a table chart. Meaning if the calculation results in  a negative value it should assign the 100%-' but only if the category is a cost since I have both revenue and costs in the Category,I'm using this on a field where I'm calculating % change.  

Regards

Matlotlo

nkosinde13
Creator
Creator
Author

@lironbaram  I just realised that  I did not phrase my question correctly:

This is I basically  what I wanted:

Num(Sum({<$(eSetFYTD_CN)>}Amount) / $(ThouMenu),'# ##0')< 0  AND  Dim Category = 'Costs'.

This is because I did not want to filter the results on category but just to assign to the values with Costs as category

Thank you

Matlotlo

Brett_Bleess
Former Employee
Former Employee

Have a look at the following Design Blog post, I think it may be of some help on this one:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
nkosinde13
Creator
Creator
Author

Thank you so much for this @Brett_Bleess , it really is helpful. I now have  a better understanding of aggregate function.