topic Re: Error with count if for 2 conditions in QlikView

Error with count if for 2 conditions

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

Good day,

New to Qlik and mostly self taught, so all of the fancy things still escape me.

I'm having difficulty with a Count if multiple conditions met expression. Basically, I want the formula to calculate when an ICCID (SERIAL_NO) has both Ported (PORT_ICCID is not blank) and Connected (CONNECTION_ICCID is not blank). The Expression (one of many that finally worked) that I came up with is:

If(((Count(DISTINCT([PORT_ICCID]))) =1 and Count(DISTINCT([CONNECTION_ICCID])) =1), COUNT(DISTINCT([SERIAL_NO])))

And, even though this expression populates within the expanded table... it is not totaling in the sub totals.

If you could please help me figure out where I went wrong

Thank you

Re: Error with count if for 2 conditions

Frank_Hartmann — Thu, 12 Sep 2019 10:25:13 GMT

maybe this:

sum(If(((Count(DISTINCT([PORT_ICCID]))) =1 and Count(DISTINCT([CONNECTION_ICCID])) =1), COUNT(DISTINCT([SERIAL_NO]))))

Re: Error with count if for 2 conditions

Danelle — Thu, 12 Sep 2019 10:28:16 GMT

Hey Frank, thanks, but is is not working

Re: Error with count if for 2 conditions

Frank_Hartmann — Thu, 12 Sep 2019 10:31:37 GMT

and this?

sum(If(((Count(DISTINCT([PORT_ICCID]))) =1 and Count(DISTINCT([CONNECTION_ICCID])) =1), COUNT(DISTINCT([SERIAL_NO])),0))

Re: Error with count if for 2 conditions

sunny_talwar — Thu, 12 Sep 2019 10:31:42 GMT

Try this

Count(DISTINCT {<[SERIAL_NO] = {"=Count(DISTINCT [PORT_ICCID]) = 1 and Count(DISTINCT [CONNECTION_ICCID]) = 1"}>} [SERIAL_NO])

Re: Error with count if for 2 conditions

asinha1991 — Thu, 12 Sep 2019 10:34:29 GMT

assuming that you want to check PORT_ICCID and CONNECTION_ICCID for each row, and add a indicator 1 or 0 to indicate that in port and then have subtotal showing all such port

count(if(PORT_ICCID=1 and CONNECTION_ICCID=1, 1))

if not I want to understand how are the relationship between field for ex:one to many

Re: Error with count if for 2 conditions

Danelle — Thu, 12 Sep 2019 10:36:55 GMT

Thanks Sunny, that worked brilliantly.

You've made my day, half of the brownie points I'm scoring with the boss belong to you!

Have a great day!