Confused by 'OR' behavior

[Markbhai]

Hi all.

I am confused by the behaviour of the OR function in an If statement and woudl like to:

a. Understand how it works and b, understand how to achieve my goal.

I am using this function:

if([Request Status]='New' OR [Request Status] = 'Under Management',
count(if([Target Date]<=Today(),[id])))

The objective is to count all [id] when the target date has passed, but only for new tickets and tickets under management.

The results I get is that it works if I select 'New' and it works if I select Under Management' but it does not work (returns Null) if I select both.

Cheers

Mark

[Parthiban]

Try this part in set expression..

like

count({<[Target Date]<={'Today_Variable'}>}Id))

[vikasmahajan]

try with + or operator in set analysis  : 

refer: https://community.qlik.com/t5/QlikView-App-Dev/Set-Analysis-with-OR-Condition-performance/td-p/82821...

Count({< < [Request Status]={'New'} > + < [Request Status] = {'Under Management'}>,   [Target Date] = {"<= $(=max(Today()))"}>} Quantity)

Regards

Vikas

[Markbhai]

Sorry, but I am not understanding what you mean?

Do you mean that my full expression should be:

if([Request Status]='New' OR [Request Status] = 'Under Management',
count({<[Target Date]<={'Today_Variable'}>}Id))

I get 'error in expression' when trying to edit it that way.

[Parthiban]

Hi, Try this one

If([Request Status]='New' OR [Request Status] = 'Under Management',
count({<[Target Date]={'<=$(Today_Variable)'}>}Id))

'Today_Variable' means you need to create one variable. That variable contains today date.

[Aditya_Chitale]

If you want to show count in KPI only after making selections on Request Status field for New & Under Management tickets, Use below expression:

if(getselectedcount([Request Status]) > 0,

Count(Aggr(if([Request Status]='New' OR [Request Status] = 'Under Management',
count(if([Target Date]<=Today(),[id]))),[Request Status]))

,

null()

)

Here, I have used the same expression provided by you. Just added Aggr() & count() on top of it. Aggr() Helps in grouping the data similar to "group by" & count() combined with Aggr() returns you the combined count after grouping. Hope I am making sense.

If you want to learn more about Aggr() refer below link:

Aggr - chart function | Qlik Sense on Windows Help

Regards,

Aditya

[vincent_ardiet_]

You have NULL results because when you select multiple values [Request Status]='New' is wrong, as request status contains not only New, same thing for your test with under management.

For example, this would work, but this is not perfect and works well because you only have 2 values:
if(MinString([Request Status])='New' OR MaxString([Request Status]) = 'Under Management',
count({<[Target Date]<={'Today_Variable'}>}Id))

What do you want to see if nothing is select in Request Status?
If you want to see New and Under Management in this case, you can write your expression like this which avoid to use the IF (IF are not good for performances):
count({<[Request Status]*={'New','Under Management'},[Target Date]<={'Today_Variable'}>}Id)

[Markbhai]

I got it working correctly with the following (Thanks to everyones help)

Sum(Aggr(if([Request Status]='New' OR [Request Status] = 'Under Management',
count(if([Target Date]<=Today(),[id])
)),[Request Status]))

Whilst I really liked the look of Vincents response (to do away with the IF statements - I could not get it to work.

Thanks

All

[vincent_ardiet_ · Accepted solution]

Yes, sorry, there is an error in the last part of the set analysis you wrote initially and I have not checked it:
count({<[Request Status]*={'New','Under Management'},[Target Date]={"<=$(=Today())"}>}Id)

[Markbhai]

Thanks Vincent.

This is much prettier.