topic If statement with where clause in expression in QlikView

If statement with where clause in expression

nkosinde13 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: If statement with where clause in expression

lironbaram — Thu, 21 Nov 2019 08:30:32 GMT

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

Re: If statement with where clause in expression

nkosinde13 — Thu, 21 Nov 2019 08:55:18 GMT

@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

Re: If statement with where clause in expression

nkosinde13 — Thu, 21 Nov 2019 09:22:34 GMT

@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

Re: If statement with where clause in expression

Brett_Bleess — Wed, 04 Dec 2019 21:14:08 GMT

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

Re: If statement with where clause in expression

nkosinde13 — Fri, 06 Dec 2019 13:11:40 GMT

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