Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be try this?
= Count(DISTINCT {< FINALMQLSTATUS -= {'Not MQL'} >} [Name])
OR
= Count(DISTINCT IF(NOT Match(FINALMQLSTATUS, 'Not MQL'), [Name]))
Why are you taking the opposite approach... why not this?
= Count(DISTINCT {< FINALMQLSTATUS = {'MQL'} >} [Name])
Hahah. Yes you are right. But was not sure what other status values that field holds so thought just exclude Not MQL value.
If there are other values, then your expression will be completely wrong... isn't it? OP seems to want to sum only Status MQL...
Oops. I missed that. Got it.
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))
Try this
Count({<IDforUTDAcctswithOpps = {"=Len(Trim(IDforUTDAcctswithOpps)) <>0"}, MARKETINGPROGRAM -= {'*Advanced*', '*Cross-Sell*'}>} DISTINCT IDforUTDAcctswithOpps)
Or this
Count({<IDforUTDAcctswithOpps = {"*"}, MARKETINGPROGRAM -= {'*Advanced*', '*Cross-Sell*'}>} DISTINCT IDforUTDAcctswithOpps)
So this formula would actually ignore the IDs with programs that contain either word, correct?
I would think so.... unless I missunderstood the issue... have you tried it?