Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

Re: If statement with where clause in expression

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

Highlighted
Creator
Creator

Re: If statement with where clause in expression

@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

Highlighted
Creator
Creator

Re: If statement with where clause in expression

@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

Highlighted
Digital Support
Digital Support

Re: If statement with where clause in expression

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.
Highlighted
Creator
Creator

Re: If statement with where clause in expression

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