6 Replies Latest reply: Apr 26, 2012 8:42 AM by Giovanne Bergstein RSS

    Date range to one specific day

      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

        • Date range to one specific day
          Jose Tos

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

          • Date range to one specific day
            Jagan Nalla

            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;

              • Re: Date range to one specific day

                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


                  • Re: Date range to one specific day
                    Jagan Nalla

                    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.

                    Untitled.png