How to get lost customers in weekly trend?

[wenchia]

I'd like to find the number of customers who purchase last week but did not purchase the current week.
I used the expression below and it works only when I show it in the text object.  Lost customer of the latest week

=count({$<WEEKNO=,CUSTID={'=count({1<WEEKNO={$(PrevWeek)}>} CUSTID)>0 and (count({1<WEEKNO={"$(=max(WEEKNO))"}>} CUSTID)=0 )'}>}distinct CUSTID)
 

However, currently I would like to get a weekly trend with Week as dimension in a bar chart to show every week how many lost customers compared to the previous week, by using the same expression, it did not work.

I'm hoping to get a chart with

201802.  2 customers

201803   1 customers

201804.  3 customers

Please help.

Attached is the example with data

[ogautier62]

Hi,

interesting !

the only way I see is that :

in your calendar add a column NextWeek which is week+1

then make a sheet with

week, customer in dimension

and an expression :

aggr(count with set analysis week = NextWeek .......  = 0)

you'll have customer with commands for the week and not next

make count total week in another column

regards

[wenchia]

Hi Olivier,

I don't quite get what you mean.  Do you mind giving an example?

Thanks

[isingh30]

Can you share data in excel format?

Thanks.

[wenchia]

I have the data in the script using load inline.

WEEKNO, CUSTID
     201801, 1001
     201801, 1002
     201801, 1003
     201801, 1004
     201801, 1005
     201802, 1001
     201802, 1002
     201802, 1003
     201802, 1006
     201803, 1004
     201803, 1001
     201803, 1003
     201803, 1006
     201804, 1004
     201804, 1007
     201804, 1008
]

[ogautier62]

you can try this :

outer join (your table) load (WEEKNO-1) as WEEKNO, CUSTID , WEEKNO as NextW resident your table;

if your customer is lost then field NextW xill be NULL

in your sheet dimension : WEEKNO, CUSTID

and expression : if(isnull(NextW),1) for lost customers,

sum this field to have total

[wenchia · Accepted solution]

Thanks Olivier for the idea.

What I did was adding curerntWeekFlag and PreviousWeekFlag along with the code suggested.

If the currentWeekFlag is null and previousWeekFlag is 1, we will count it as Lost.

Tab:

LOAD * INLINE [
     WEEKNO, CUSTID, CurWFlag
     201801, 1001, 1
     201801, 1002, 1
     201801, 1003, 1
     201801, 1004, 1
     201801, 1005, 1
     201802, 1001, 1
     201802, 1002, 1
     201802, 1003, 1
     201802, 1006, 1
     201803, 1004, 1
     201803, 1001, 1
     201803, 1003, 1
     201803, 1006, 1
     201804, 1004, 1
     201804, 1007, 1
     201804, 1008, 1
];

Load max(WEEKNO) as MaxWeek
resident Tab;

LET vMaxWeek = floor(peek('MaxWeek'));

outer join (Tab)

  Load
  WEEKNO+1 as WEEKNO,
  CUSTID,
  '1' as PreWFlag 
resident Tab
  where WEEKNO < $(vMaxWeek)  ;
// MaxWeek is check so we will not create a new Week (Week + 1)

In the chart I simply put

sum(if (isnull(CurWFlag) and PreWFlag = 1   , 1,0))