Error with count if for 2 conditions

[Danelle]

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

[Frank_Hartmann]

maybe this:

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

[Danelle]

Hey Frank, thanks, but is is not working

[Frank_Hartmann]

and this?

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

[sunny_talwar · Accepted solution]

Try this

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

[asinha1991]

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

[Danelle]

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!