topic Re: OR operator in set analysis in QlikView

OR operator in set analysis

Anonymous — Tue, 24 Jan 2017 14:25:08 GMT

I have created the following set analysis to try and count the number of qualified leads based on three fields:

- Lead Score

- Pardot Score

- Lead Stage

count({< [Lead Stage] = {'MQL'}>}distinct([Lead ID])) + count({< [Lead Score] = {">=100"}>}distinct([Lead ID])) + count({< [Pardot Score] = {">=100"}>}distinct([Lead ID]))

Not all of the fields are in sync, so I want to count all of the qualified leads WITHOUT double counting (i.e a lead has a score > 100 and a lead stage = MQL). In SQL it would be something like, WHERE [Lead Stage] = 'MQL' OR [Lead Score] >= 100 OR [Pardot Score] >= 100 ... But the union in set analysis is definitely double counting records.

Any help is appreciated.

Thank you,

- dave

Re: OR operator in set analysis

vinieme12 — Tue, 24 Jan 2017 14:35:54 GMT

try

count({< [Lead Stage] = {'MQL'}>+ <[Lead Score] = {">=100"}> + < [Pardot Score] = {">=100"}>}distinct([Lead ID]))

Re: OR operator in set analysis

krishnacbe — Tue, 24 Jan 2017 14:43:18 GMT

Hi,

You can use below expression.

count({< [Lead Stage] = {'MQL'}>} + [Lead Score] = {">=100 + [Pardot Score] = {">=100"}>}distinct [Lead ID])

Re: OR operator in set analysis

sunny_talwar — Tue, 24 Jan 2017 14:51:45 GMT

Or just this:

Count({<[Lead ID] = {"=[Lead Stage] = 'MQL' or [Lead Score] >=100 or [Pardot Score] >=100"}>} DISTINCT [Lead ID])

Re: OR operator in set analysis

Anonymous — Tue, 24 Jan 2017 14:55:41 GMT

You did it again Sunny. Many thanks as always