Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
So each 25th will belong to two ranges at the same time??
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;
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
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.
If your data is huge plz use this expression in preceding load.
Thanks Jagan Nalla, perfect solution