topic Re: If condition with multiple criteria using AND, also value if TRUE is aggregation in QlikView

If condition with multiple criteria using AND, also value if TRUE is aggregation

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

I'm trying to write a condition on some of my bar charts that would be equivalent to SUMIFS or COUNTIFS variations in Excel. If my IF condition meets two criteria (AND) then I want two values aggregated (summed)

here's what I have written:

=if(FCRA_dummy=1 AND [Not_e-Oscar_Dum]=1, count(Pkey)) + count([ACDV Control Number])

IF both of my dummies are "1", I just want a count of a distinct column in my data, then sum that with another count of a distinct column in my data. I am not getting an error with this expression, but it isn't showing any data, and I know that's not true. I'm sure there is a simple solution to this, or maybe even a function built in to QV.. Any ideas on why this isn't working?

Re: If condition with multiple criteria using AND, also value if TRUE is aggregation

sunny_talwar — Mon, 17 Oct 2016 16:46:25 GMT

How about this:

=If(FCRA_dummy = 1 and [Not_e-Oscar_Dum] = 1, Count(Pkey) + Count([ACDV Control Number]))

=Count({<FCRA_dummy = {1}, [Not_e-Oscar_Dum] = {1}>} Pkey) + Count({<FCRA_dummy = {1}, [Not_e-Oscar_Dum] = {1}>}[ACDV Control Number])

Re: If condition with multiple criteria using AND, also value if TRUE is aggregation

lucasdavis500 — Mon, 17 Oct 2016 17:20:40 GMT

I did indeed use Set Analysis to solve this issue. Thanks