topic Date range to one specific day in QlikView

Date range to one specific day

giovanneb — Tue, 24 Apr 2012 13:53:52 GMT

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

chematos — Tue, 24 Apr 2012 14:17:04 GMT

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

Date range to one specific day

jagannalla — Tue, 24 Apr 2012 14:18:52 GMT

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

giovanneb — Tue, 24 Apr 2012 19:20:40 GMT

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

jagannalla — Wed, 25 Apr 2012 08:41:14 GMT

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.

Re: Date range to one specific day

jagannalla — Wed, 25 Apr 2012 08:48:01 GMT

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

Date range to one specific day

giovanneb — Thu, 26 Apr 2012 12:42:32 GMT

Thanks Jagan Nalla, perfect solution