Counts in pivots-QlikView

[Anonymous]

I have a table like this. when I pivot it, I only want to do a distinct count of Names with Status MQL, I want the count to ignore the names associated with the not MQL status. Here is the expression I thought would work:

if(FINALMQLSTATUS='MQL',Count (DISTINCT [Name]),0)      

Name	FINALMQLSTATUS
Bob	MQL
Jim	Not MQL
Sally	MQL

However, it still counts all of them and seems to ignore the if statement. Please help. What am I doing wrong? Thank you

[vishsaggi]

May be try this?

= Count(DISTINCT {< FINALMQLSTATUS -= {'Not MQL'} >} [Name])

OR

= Count(DISTINCT IF(NOT Match(FINALMQLSTATUS, 'Not MQL'), [Name]))

[sunny_talwar]

Why are you taking the opposite approach... why not this?

= Count(DISTINCT {< FINALMQLSTATUS = {'MQL'} >} [Name])

[vishsaggi]

Hahah. Yes you are right. But was not sure what other status values that field holds so thought just exclude Not MQL value.

[sunny_talwar]

If there are other values, then your expression will be completely wrong... isn't it? OP seems to want to sum only Status MQL...

[vishsaggi]

Oops. I missed that. Got it.

[Anonymous]

Thank you both. A somewhat related question.

In my other pivot, I want to count distinct non-empty IDs (they are called IDforUTDAcctswithOpps below). However, I only want to count ithem for Marketing programs that do not contain the words "advanced" or "Cross-sell". If the program contains either word, I don't want the Id to be included in the count. Would the formula below work?

if(WildMatch(MARKETINGPROGRAM,'*Advanced*')=1 or WildMatch(MARKETINGPROGRAM,'*Cross-Sell*')=1,0, count({< IDforUTDAcctswithOpps = {"=Len(Trim(IDforUTDAcctswithOpps)) <>0"} >} DISTINCT IDforUTDAcctswithOpps))

[sunny_talwar]

Try this

Count({<IDforUTDAcctswithOpps = {"=Len(Trim(IDforUTDAcctswithOpps)) <>0"}, MARKETINGPROGRAM -= {'*Advanced*', '*Cross-Sell*'}>} DISTINCT IDforUTDAcctswithOpps)

Or this

Count({<IDforUTDAcctswithOpps = {"*"}, MARKETINGPROGRAM -= {'*Advanced*', '*Cross-Sell*'}>} DISTINCT IDforUTDAcctswithOpps)

[Anonymous]

So this formula would actually ignore the IDs with programs that contain either word, correct?

[sunny_talwar]

I would think so.... unless I missunderstood the issue... have you tried it?