Expression to Count distinct, provided IF OR conditions are satisfied

[#1]

Hi

Im stuck in a expression where i have to count distinct ids provided a set of conditions are satisfied.

There are 3 rules, Rule1, Rule 2, Rule 3 for example. I have to only count those IDs which satisfy either Rule 1 or Rule 2 or Rule 3. i.e if one ID satisfies more than 1 rule , i should count it only once.

Rule1:

Number of invoices > 100

Rule 2:

Sum of invoice amount > $1000

Rule 3:

Percentage increase > 4%

COUNT DISTINCT ID from all of the below(

if( number of invoices >100, then count distinct ID)   --- example 3 distinct IDs satisfy this rule

+ if ( sum of invoice amount >$1000, then count distinct ID)  -- ex, 2 distinct IDs satisfy this rule

+ if( percentage increase >4%, then count discinct ID) )   -- ex 3 distinct ID satisfy this rule,

Then my result should be 3+2+3  = 8,

In case 2 IDs that satisfy rule 2, also satisfy rule 1 , then it should be counted only once and the result should be: 6.

Please suggest a way to convert the above requirement into a qlikview expression. I tried if, iif else , aggr , it dint work fine,; the rules are written as set analysis expression.

[sunny_talwar]

May be something like this (assuming Number of invoices, Sum of invoice amount and Percentage increase are field names in your application)

Count(DISTINCT {<ID = p({<[Number of invoices] = {'>100'}>}) + p({<[Sum of invoice amount] = {'>1000'}>}) + p({<[Percentage increase] = {'>0.04'}>})>} ID)

You might need to change the last part to this if percentage increase is formatted in %

Count(DISTINCT {<ID = p({<[Number of invoices] = {'>100'}>}) + p({<[Sum of invoice amount] = {'>1000'}>}) + p({<[Percentage increase] = {'>4%'}>})>} ID)

[swuehl · Accepted solution]

If you already have expressions to check for fulfillment of each single rule, then please, post these expressions. It's just saving us a lot of time if you describe your setting as best as you can.

It could look like

=COUNT(

{<ID = {"=Count(DISTINCT Invoice)>100 OR Sum(InvoiceAmount) > 1000 OR Only(PercentIncrease)>0.04"}>}

DISTINCT ID)

You need to adapt the three expressions to your rules.

[#4]

thank you. i modified and it worked