Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
giovanneb
Creator II
Creator II

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

1 Solution

Accepted Solutions
jagannalla
Partner - Specialist III
Partner - Specialist III

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

View solution in original post

6 Replies
chematos
Specialist II
Specialist II

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

jagannalla
Partner - Specialist III
Partner - Specialist III

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
Creator II
Creator II
Author

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
Partner - Specialist III
Partner - Specialist III

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

jagannalla
Partner - Specialist III
Partner - Specialist III

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

giovanneb
Creator II
Creator II
Author

Thanks Jagan Nalla, perfect solution