Loyalty Points dashboard

[salonibhatia]

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	Gold	2018-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
Silver	200
Gold	200
Platinum	0

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

membership_code	purchase_amt
Silver	0
Gold	600
Platinum	350

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

[duncanblaine]

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.

[salonibhatia]

@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_code	purchase_amt
Silver	200
Gold	200
Platinum	0

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...

[duncanblaine · Accepted solution]

Hi Saloni

I think I finally worked it out.

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‌

[duncanblaine]

salonibhatia‌ Did this work for you?

[salonibhatia]

duncanblaine‌...Thanks a ton ...that worked ...sorry couldnt check since past few days