5 Replies Latest reply: May 19, 2018 6:32 AM by Saloni Bhatia RSS

    Loyalty Points dashboard

    Saloni Bhatia

      I have a situation wherein I want to show the latest membership type and cumulate all points of that member against it rather than cumulating it against all membership types it had in that specific period

      eg:

      say I have below table:

       

         

      member_no membership_code Join_date Reg_date Change_date purchase_amt
      12abc Silver 2018-04-08 2018-04-10 2018-04-10 100
      12abc Gold 2018-04-08 2018-04-10 2018-04-11 100
      12abc Gold 2018-04-08 2018-04-10 2018-04-13 50
      12abc Platinum 2018-04-08 2018-04-10 2018-04-14 100
      13abc Silver 2018-04-09 2018-04-10 2018-04-10 200
      13abc Gold 2018-04-09 2018-04-10 2018-04-13 200
      13abc Gold2018-04-09 2018-04-10 2018-04-14 200

       

       

       

      if someone selects date= 2018-04-11

      Then data should look like below

       

         

      membership_code purchase_amt
      Silver200
      Gold200
      Platinum0

       

       

      if someone selects 2018-04-14 then data should look like

         

      membership_code purchase_amt
      Silver0
      Gold600
      Platinum350

       

       

       

      all cumulative points of the member going against latest status only. Also, one can select just 2 dates then cumulative points of those 2 dates will go against the latest membership_code as of that date

        • Re: Loyalty Points dashboard
          Duncan Blaine

          Hi Saloni

          Are you sure those number in your example outcomes are correct?
          I tried a few scenarios using the dates you provided and couldnt get the purchase_amt to ever Sum to the right value.

           

          This expression might help you. The sum of  all purchase_amt's where the Change_date is less than the selected Change_date.

           

          Sum({<Change_date={"<=$(=Max(Change_date))"}>}purchase_amt)

           

          Hope this is helpful to you, otherwise please explain those numbers in your example, and which date you're selecting.

            • Re: Loyalty Points dashboard
              Saloni Bhatia

              @ducan blaine

              This is one part of it...the part that I was not able to solve is how to cumulate the points against the latest membership_code

               

              in the above example

              if someone selects date= 2018-04-11

              Then data should look like below

               

               

              membership_codepurchase_amt
              Silver200
              Gold200
              Platinum0

               

              the above result set is calculated as below:

               

              for member_no 12abc the latest membership_code is    Gold based on change_date...thus cumulating all his points against Gold(100+100)

              for member_no 13abc the latest membership_code is    Silver based on change_date...thus cumulating all his points against Silver(200)

               

               

              similarly for 2018-04-14

              for member_no 12abc the latest membership_code is    Platinum based on change_date...thus cumulating all his points against Platinum(100+100+ 50 + 100)

              for member_no 13abc the latest membership_code is    Gold based on change_date...thus cumulating all his points against Gold(200+ 200 +200)

               

               

              Hope this explains the issue...

               

              will also have to later on add cumulation of points based on selected dates/year/quarter...say if data is for 2017 & 2018 and someone selects 2018...then only cumulative points of 2018 to be shown...

            • Re: Loyalty Points dashboard
              Duncan Blaine

              Hi Saloni

              I think I finally worked it out.

              MembershipPoints.PNG

              MembershipPoints2.PNG
              There might be an easier way, but I couldn't see it!

              I had to use a calculated dimension to create the table and the expressions are quite long.

              ie..

              Dimension:

                    =ValueList('Platinum','Gold','Silver')

               

              Expression:

              pick( RowNo(),

              Sum({$<

              Change_date={"<=$(=Max(Change_date))"},

              member_no={$(=chr(39)&CONCAT(distinct {<Change_date={"<=$(=Max(Change_date))"},membership_code={'Platinum'}>}member_no,chr(39)&','&chr(39))&chr(39))}

              >}purchase_amt),

              Sum({$<

              Change_date={"<=$(=Max(Change_date))"},

              member_no=

              {$(=chr(39)&CONCAT(distinct {<Change_date={"<=$(=Max(Change_date))"},membership_code={'Gold'}>}member_no,chr(39)&','&chr(39))&chr(39))}

              -{$(=chr(39)&CONCAT(distinct {<Change_date={"<=$(=Max(Change_date))"},membership_code={'Platinum'}>}member_no,chr(39)&','&chr(39))&chr(39))}

              >}purchase_amt),

              Sum({$<

              Change_date={"<=$(=Max(Change_date))"},

              member_no=

              {$(=chr(39)&CONCAT(distinct {<Change_date={"<=$(=Max(Change_date))"},membership_code={'Silver'}>}member_no,chr(39)&','&chr(39))&chr(39))}

              -{$(=chr(39)&CONCAT(distinct {<Change_date={"<=$(=Max(Change_date))"},membership_code={'Platinum'}>}member_no,chr(39)&','&chr(39))&chr(39))}

              -{$(=chr(39)&CONCAT(distinct {<Change_date={"<=$(=Max(Change_date))"},membership_code={'Gold'}>}member_no,chr(39)&','&chr(39))&chr(39))}

              >}purchase_amt)

              )

               

               

              loyalty points_system