topic Counts in pivots-QlikView in QlikView

Counts in pivots-QlikView

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

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

Re: Counts in pivots-QlikView

vishsaggi — Tue, 24 Jul 2018 14:48:59 GMT

May be try this?

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

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

Re: Counts in pivots-QlikView

sunny_talwar — Tue, 24 Jul 2018 15:01:51 GMT

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

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

Re: Counts in pivots-QlikView

vishsaggi — Tue, 24 Jul 2018 15:06:16 GMT

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

Re: Counts in pivots-QlikView

sunny_talwar — Tue, 24 Jul 2018 15:08:20 GMT

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

Re: Counts in pivots-QlikView

vishsaggi — Tue, 24 Jul 2018 15:10:36 GMT

Oops. I missed that. Got it.

Re: Counts in pivots-QlikView

Anonymous — Tue, 24 Jul 2018 15:45:35 GMT

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))

Re: Counts in pivots-QlikView

sunny_talwar — Tue, 24 Jul 2018 15:52:07 GMT

Try this

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

Or this

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

Re: Counts in pivots-QlikView

Anonymous — Tue, 24 Jul 2018 15:58:30 GMT

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

Re: Counts in pivots-QlikView

sunny_talwar — Tue, 24 Jul 2018 16:03:45 GMT

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

Re: Counts in pivots-QlikView

Anonymous — Tue, 24 Jul 2018 16:06:33 GMT

I am about to. Thanks

Re: Counts in pivots-QlikView

Anonymous — Tue, 24 Jul 2018 16:22:02 GMT

A question about your expression above. What if I have two conditions instead of one? For instance, suppose I want to only count the name if FINALMQLSTATUS = 'MQL' AND [product interest lexi]='Yes'. What would that syntax look like?

Re: Counts in pivots-QlikView

Anonymous — Tue, 24 Jul 2018 16:23:28 GMT

I am referring here to the original response you gave where you suggested to use = Count(DISTINCT {< FINALMQLSTATUS = {'MQL'} >} [Name])

Re: Counts in pivots-QlikView

sunny_talwar — Tue, 24 Jul 2018 16:29:47 GMT

Just this

Count(DISTINCT {<FINALMQLSTATUS = {'MQL'}, [product interest lexi] = {'Yes'}>} [Name])