5 Replies Latest reply on Jan 28, 2014 8:11 AM by Joshua Milligan

    How to make excluding filters

    Ferran Pijoan
    Ferran Pijoan Dec 17, 2013 8:36 AM

      Hi!


      I am trying to filter users (customers) and be able to create groups of them depending on what platform they have used.

      An example similar to my data would be (attached file too):

       

      User      Platform              Revenue

      1             Visa                       2

      2             Visa                       4

      1             Pay Pal                 2

      2             AMEX                   3

      3             Master Card      1

      4             Visa                       2

      4             Pay Pal                 4

      1             Pay Pal                 1

       

      I would like to create groups of users that have used 2 specific platforms, or 3 specific platforms.

       

      For example, I would group the users that have used "Visa & Pay Pal" platforms. Resulting the users 1 and 4.

      Or I would group the users that have used "Visa & AMEX" platforms. Resulting just the user 2.

      Or group the users that have used "Pay Pal & AMEX & Master Card". Resulting 0 users.

      Or group the users that have used "AMEX & Master Card". Resulting 0 users.

       

      I've tried to get that using Sets and combining them, but it didn't work at all.

       

      Any ideas?

       

      Thanks in advance.

        • 1. Re: How to make excluding filters
          Joshua Milligan
          Joshua Milligan Dec 31, 2013 11:49 AM (in response to Ferran Pijoan)

          Ferran,

           

          I've attached a workbook to demonstrate a possible approach.

           

          Here's the basic setup:

          0.png

           

           

           

          I'm going to have a calculation that gets the total number of selected platforms.

           

          [Total # Selected Platforms]

          TOTAL(COUNTD(Platform))

           

          Then I'll compare, per User the number of records with the number of platforms and keep only those that match.  Notice above that the Total # of Selected Platforms is always 2.  The number of records per user varies.  User 2 is the only one that matches.  By moving the calculation to the filters, I can keep only User 2 (when AMEX and Visa are the two selected).

           

          Hope that helps!

           

          Happy New Year!

          Joshua

          • 2. Re: Re: How to make excluding filters
            Ferran Pijoan
            Ferran Pijoan Jan 28, 2014 3:16 AM (in response to Joshua Milligan)

            Hey Joshua,

             

            First of all, thanks very much for replying. I really appreciate the time you've dedicated on this.

             

            Unfortunatelly, this approach is not working to my data. I've created the same calculated fields and the data I visualize doesn't match my criteria.

             

            I was wondering why, when I realised that your approach doesn't work 100%. Case:

             

            If I filter by PayPal & Visa I should visualize user 1 and user 4. And I only see user 4.

            This happens because user 1 has paid three times, twice with Paypal and once with Visa.

            The SUM([Number of records]) is higher than the # selected platforms.

            SUM([Number of records]) = 3 and [Total # Selected Platforms] = 2.

             

            I've solved that out changing the calculated field 'Keep User Who has All Selected' to: IF COUNTD([Platform]) == [Total # Selected Platforms] THEN 1 END

             

            I've  attached the workbook with the new formula. So, if anyone want to use that filter, use that formula.

             

            This exercise is very useful to recognise which users have paid with different platforms (and with which platforms).

            But, I'd like to be able to group the users, so I could work with these user segmentations to discover other info. And this is not possible using that approach.

             

            What do you think about?

            Do you have any other idea on how we could make this possible?

             

            Thanks again.

             

            Ferran

            • 3. Re: Re: Re: How to make excluding filters
              Joshua Milligan
              Joshua Milligan Jan 28, 2014 6:18 AM (in response to Ferran Pijoan)

              Ferran,

               

              How about using some sets for this kind of analysis.  You could create a set for each type of user (e.g. AMEX Users or Visa Users) then you could create combined sets for the combinations you want to analyze (e.g Visa AND Amex Users).

               

              The set would be a conditional set, using a formula to determine if the user had that type of card.  Here's the setup for the original set:

               

               

              0.png

               

              Then to combine the sets, CTL + Click to select two of them and right click and select "Create Combined Set":

               

              1.png

               

              This will be all users who have Visa and AMEX (only User 2 in the sample data).

               

               

              Then you can use the set to filter or show who's in and out of the set.

               

              -1.png

               

              The IN/Out functionality is not available with data sources using the MS JET driver (Excel, Access, Text files) , but you can extract those if needed (also not available with OLAP).

               

              Hope that gives an alternate approach that helps!

               

              Regards,

              Joshua

              • 4. Re: How to make excluding filters
                Ferran Pijoan
                Ferran Pijoan Jan 28, 2014 7:26 AM (in response to Joshua Milligan)

                Joshua,

                 

                Thank you very much. This really solves my issue.

                 

                Regards,

                 

                Ferran

                • 5. Re: How to make excluding filters
                  Joshua Milligan
                  Joshua Milligan Jan 28, 2014 8:11 AM (in response to Ferran Pijoan)

                  Great!  Glad to help!

                  -Joshua