Date range to one specific day

[giovanneb]

Hello I need to create a data range, where the start date will always be the 25th of each month and the end date the 26th of each month, I have the date field sales how can I get this review in the load:

Example:  Start date: 25/12/2011; End Date: 26/01/2012

Sale Date = 20/12/2011, 21/12/2011, 22/12/2011, 23/12/2011, 24/12/2011, 26/12/2011, 27/12/2011, 28/12 / 2011,

29/12/2011, 30/12/2011, 31/12/2011, 02/01/2012, 03/01/2012, 04/01/2012, 01/05/2012, 06/01/2012 , 01/07/2012 08/01/2012,

09/01/2012, 01/10/2012, 01/11/2012, 01/12/2012, 13/01/2012, 14/01/2012, 01/15/2012, 01/16/2012, 17/01/2012, 01/18/2012, 01/19/2012, 20/01/2012

21/01/2012, 01/22/2012, 23/01/2012, 01/24/2012, 01/25/2012, 01/26/2012

01/27/2012, 28/01/2012, 29/01/2012 ..........

I am waiting, thanks

[chematos]

So each 25th will belong  to two ranges at the same time??

[jagannalla]

Hello,

Try this code, Hope it helps you.

LOAD Sale Date,

     If(Day(Sale Date)='25',Date(Sale Date,'DD/MM/YYYY')) as StartDate,

     If(Day(Sale Date)='26',Date(Sale Date,'DD/MM/YYYY')) as EndDate

FROM

Table;

[giovanneb]

Period (Monthly - 26 to 25)

Sales Date Date Start  Date end

01/12/2011          25/11/2011          26/12/2011

17/12/2011          25/11/2011          26/12/2011

25/12/2011          25/11/2011          26/12/2011

26/12/2011          25/11/2011          26/12/2011

27/12/2011          25/12/2011          26/01/2012

01/12/2012          25/12/2011          26/01/2012

23/12/2012          25/12/2011          26/01/2012

[jagannalla · Accepted solution]

Hello,

Try this code. It gives you want you want as per above description.

DateData:

LOAD DateData,

//     If(Day(DateData)='25',Date(DateData,'DD/MM/YYYY')) as TempStartDate,

//     If(Day(DateData)='26',Date(DateData,'DD/MM/YYYY')) as TempEndDate,

     If(Day(DateData)<='26',MakeDate(Year(DateData),Month(AddMonths(DateData,-1)),25),

                             MakeDate(Year(DateData),Month(DateData),25)) as StartDate,

     If(Day(DateData)<='26',MakeDate(Year(DateData),Month(DateData),26),

                             MakeDate(Year(DateData),Month(AddMonths(DateData,1)),26))as EndDate

FROM

Test.xlsx

(ooxml, embedded labels, table is Sheet1);

- It plots the data as shown in below image. Check once before using the code and confirm me that is you want.

[jagannalla]

If your data is huge plz use this expression in preceding load.

[giovanneb]

Thanks Jagan Nalla, perfect solution