If statement with where clause in expression

[nkosinde13]

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

[lironbaram]

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]

@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]

@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]

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

[nkosinde13]

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