Intersection: How to get customer count present in consequtive months

[ankit777]

Hi All,

I have data set as attached below.

I want to create a chart which shows count of customer id who are in current month as well as previous month( i.e. the common customer id count). The chart is like shown below.

ex.

Month Count

Jan      0

Feb      4

Mar      3

Please help.

Regards

Ankit

[#2 · Accepted solution]

Hi Ankit,

Try the following code:

Tab1:

LOAD Month,

     [Customer Id],

     AutoNumber(Month) As Month_ID

FROM

[..\Downloads\New Microsoft Excel Worksheet.xlsx]

(ooxml, embedded labels, table is Sheet1) ;

NoConcatenate

Tab3:

LOAD *,

  if(peek('Customer Id')=[Customer Id] and peek('Month_ID')=Month_ID-1,1,0) as Flag

   Resident Tab1 order by [Customer Id] ,Month_ID asc;

  DROP Table Tab1;

Regards,

Jemimah

[ankit777]

Any idea of doing this from front end and not backend scripting?