Set expression and IF not calculating as expected

[laurafinn]

Hi all,

I'm trying to count the number of loan applications where either a)  The loan end date is not populated (='1') and The status Reason = 'On Loan' 

OR 

b) The loan status = 'Active' and the expiry date of the loan is greater than the date of the reporting month.

The below 2 expressions work until I combine them:

i.e.

The below counts correctly on the basis of Scenario a:

Count ({$<[Loan End Date Flag]={'1'}>} {$<[Status Reason]={'On Loan'}>} [Loan Application ID]) 

The below counts correctly on the basis of Scenario b:

Count({$<[Loan Status 2]={'Active'}>} IF([Loan End Date] >[Max Day in Reporting Month], [Loan Application ID]))

However when I merge the two with the "+" Operator for "OR" the expression only appears to include the results of the second part of the expression (scenario b)

Count({$<[Loan End Date Flag]={'1'}>} {$<[Status Reason]={'On Loan'}> + <[Loan Status 2]={'Active'}>} IF([Loan End Date] >[Max Day in Reporting Month], [Loan Application ID]))

I feel like I'm close, can anyone help me spot what I'm missing? 

[clementcouillaud · Accepted solution]

Hi @laurafinn ,

L'expression du scénario A me semble erronée.

La bonne expression pour le scénario  A n'est-elle pas plutôt :

Count ({$<[Loan End Date Flag]={'1'},[Status Reason]={'On Loan'}>} [Loan Application ID]) 

Dans ton cas, les scénarios ne sont-ils pas complémentaires ?

Si un enregistrement ne peut pas être dans le scénario A et B à la fois alors le plus simple consiste à sommer tes 2 expressions.

Count ({$<[Loan End Date Flag]={'1'},[Status Reason]={'On Loan'}>} [Loan Application ID]) 

+

Count({$<[Loan Status 2]={'Active'}>} IF([Loan End Date] >[Max Day in Reporting Month], [Loan Application ID]))

Sinon, pourrais-tu nous partager un extrait de ton jeu de données ?

Good luck !

Clément

[rwunderlich]

This is a case where I would use the advanced search {"=expr"} set like this:

Count(
{<[Loan Application ID] =
{"=([Loan Application ID]=1 and [Status Reason]='On Loan')
or ([Loan Status 2]='Active' and [Loan End Date] >[Max Day in Reporting Month])
"}>}
[Loan Application ID])

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[laurafinn]

This one didn't work however I wasn't familiar with the advanced search {"=expr"}  set, I'll explore that a little more!  Thank you for your help

[laurafinn]

Thank you 🙂