Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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)
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.
thank you. i modified and it worked