2 Replies Latest reply on Sep 17, 2018 8:25 AM by Andrea Clerico

    Create Groups based on multiple conditions

    Andrea Clerico

      Hello,

       

      I am trying to create sub-set of profileIDs in my database based on two conditions. Ideally I would like to tag each profileID with a code based on their group.

       

      The dimensions that decide the groups are 'products' and 'ordernumber'.

       

       

      I attached a part of the database, where profileID is unique per user.

      The groups I want to create are three:

       

      1) Users that bought only the StarterPack

      2) Users that bought the StarterPack and other products, having the StarterPack as first purchase (ordernumber = 1)

      3) Users that bought the StarterPack not as first purchase

      4) Users that did not buy the StarterPack.

       

      At the end I would like to have users tagged in this way

       

      I am attaching a workbook with the same dataset without the tags.

       

      Thank you in advance for your help.

       

      Best regards,
      Andrea

        • 1. Re: Create Groups based on multiple conditions
          Naveen B

          Hi Andrea,

           

          it seems you are expecting like below

           

           

          Create a calculation like below

           

          if ({Fixed [Profile ID]:MAX(if [Product]="starterpack" and [Ordernumber]=1  and {FIXED [Profile ID]:COUNTD([Ordernumber])}>1

          then

          [Product] END)})="starterpack" then "Group 2"

          ELSEIF

          ({Fixed [Profile ID]:MAX(if [Product]="starterpack" and [Ordernumber]<>1  and {FIXED [Profile ID]:COUNTD([Ordernumber])}>1

          then [Product] END) })="starterpack"

          then

          "Group 3"

          ELSEIF   ({Fixed [Profile ID]:MAX(if [Product]="starterpack" and [Ordernumber]=1  and {FIXED [Profile ID]:COUNTD([Ordernumber])}=1

          then [Product] END) })="starterpack"

          then

          "Group 1"

          ELSEIF

          ISNULL(({Fixed [Profile ID]:MAX(if [Product]="starterpack" and [Ordernumber]<>1 and {FIXED [Profile ID]:COUNTD([Ordernumber])}>1

          then [Product] END) }) )

          then

          "Group 4"

          END

           

          Hope this helps Plz mark this answer as correct or helpful to close the thread

           

          BR,

          NB

          • 2. Re: Create Groups based on multiple conditions
            Andrea Clerico

            Thank you Naveen.

             

            It makes what I need.

             

            Question: Is there a way also to do the same creating sets?

             

            Best,

            Andrea