Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
giovanneb
Contributor 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

Tags (4)
1 Solution

Accepted Solutions
jagannalla
Valued Contributor III

Re: Date range to one specific day

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

6 Replies
chematos
Valued Contributor II

Date range to one specific day

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

jagannalla
Valued Contributor III

Date range to one specific day

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
Contributor II

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


jagannalla
Valued Contributor III

Re: Date range to one specific day

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
Valued Contributor III

Re: Date range to one specific day

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

giovanneb
Contributor II

Date range to one specific day

Thanks Jagan Nalla, perfect solution

Community Browser