How to get previous date of purchase as a new column?

[#1]

Is it possible to add 'Previous date of purchase' as a separate column? Please help us...

Data set
Customer ID	Date	Sales
10001	10/10/2015	15
10001	10/11/2015	20
10001	10/12/2015	34
10002	10/10/2015	13
10002	10/14/2015	22
10002	10/15/2015	22
10002	10/16/2015	13
Desired Output
Customer	Previous Purchase Date	Current Date	Purchased in Current Date?	Total Sales
10001	10/12/2015	10/16/2015	No	69
10002	10/15/2015	10/17/2015	Yes	70

[robert_mika]

Customer 1001 did not buy on 10/16/2015

There is not 10/17/2015

What is the logic behind Purchased in Current Date?

Does this help?

T1:

LOAD [Customer ID],

     Date,

     Sales

FROM

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

T2:

load

  [Customer ID] as CastID,

  min(date(Date,'DD-MM-YYYY')) as PreviousDate,

  max(Date(Date,'DD-MM-YYYY')) as CurrentDate,

  sum(Sales) as [Ttotal Sales]

  resident T1

  group by [Customer ID];

  drop table T1;

[#3]

Sorry for missing information and confusion.

Here my current date is 10/16/2015. In fact current date is always 'Report Run date' (here my report run date is 10/16/2015)

• Here customer 10001 is not purchased on 10/16/2015 (current Date). So that 4th column = No
• Same time, 10001 customer's previous purchase is 10/12/2015. Here 10001 customer previous or last purchase is 10/12/2015 (or same) because he never had a current date purchase.
• But customer 10002 has current date purchase (10/16/2015). So that 4th column = Yes.
• Here customer 10002 previous purchase is 10/15/2015.

Desired Output
Customer	Previous Purchase Date	Current Date	Purchased in Current Date?	Total Sales
10001	10/12/2015	10/16/2015	No	69
10002	10/15/2015	10/16/2015	Yes	70

[robert_mika]

So Current Date=Today?

What about the 10/16/2015 for 1001?

[tresesco]

May be like this?

Edit: Corrected according to your updated desired result.

[jonas_rezende]

Hi, Muralidhar Raguthu.

Try code below. I just  inputted 02/16/2016 for test with today date. After, adapt for your necessity.

In Script

tmp_Sales:

LOAD * Inline [

Customer, Date, Sales

10001, 10/10/2015, 15

10001, 10/11/2015, 20

10001, 10/12/2015, 34

10002, 10/10/2015, 13

10002, 10/14/2015, 22

10002, 10/15/2015, 22

10002, 02/16/2016, 13

];

NoConcatenate

Sales:

LOAD

Customer,

Date(Date) as Date,

Sales

Resident tmp_Sales

order by

Customer,

Date;

DROP Table tmp_Sales;

Sales_Transform:

LOAD

Customer,

Date(Max(Date,2))    as [Previous Purchase Date],

Date(Max(Date))  as [Current Date],

if(Max(Date) =Date(Today())

  ,Text('Yes')

  ,Text('No')

) as [Purchased in Current Date ?],

Sum(Sales) as TotalSales

resident Sales

group by

Customer;

In Design

Hope this helps!