4 Replies Latest reply: Jan 24, 2017 9:55 AM by Dave Melillo RSS

    OR operator in set analysis

    Dave Melillo

      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