Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to generate weeks between two dates?

Hi community,

If I have startDate and endDate in my data, how can I create weeks starting from the startDate field and not the start of the year. So, basically I want to populate weeks between the two dates.

Thanks

10 Replies
Anil_Babu_Samineni

May be this?

Load *, Week(Date) as Week;

LOAD Date(MinDate+IterNo()-1) as Date 

While MinDate+IterNo()-1 <= MaxDate; 

LOAD Min(startDate) as MinDate, 

     Max(endDate) as MaxDate

AutoGenerate 1;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
avkeep01
Partner - Specialist
Partner - Specialist

Hi Laleh,

I would suggest the method from Anil Babu. If you want another method you could use a Cartesion join:

Try

ExampleTable:

LOAD

[StartDate],

[EndDate]

FROM [source];

LEFT JOIN (ExampleTable) LOAD

Date

RESIDENT [Master Calendar];

NOCONCATENATE LOAD

[StartDate],

[EndDate],

Date,

WEEK(Date) AS Week

RESIDENT ExampleTable

WHERE DATE(Date ) >= DATE([StartDate]) AND DATE(Date) <= DATE([EndDate])

;

DROP TABLE ExampleTable;

Anonymous
Not applicable
Author

Thanks Anil,

Just wondering if I have to use resident load instead of autogenerate?

Anil_Babu_Samineni

May be this?

Sample:

Load * From Table;

CalendarMaster:

LOAD Week(Final_Date) As Week;

Load Date(MinDate + IterNo() -1 ) AS Final_Date While Date(MinDate + IterNo() - 1) <= MaxDate;

Load

    Min(startDate) AS MinDate,

    Max(endDate) AS MaxDate

RESIDENT Sample;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
its_anandrjs

By resident load you can try this way

let startDate  = Num(MakeDate(2015,1,1));

let endDate  = Num(MakeDate(2017,12,30));

tmpCalendar:

Load

Date($(startDate) + IterNo() -1) as Date

AutoGenerate 1

While Date($(startDate) + IterNo() -1) <= $(endDate);

Calendar:

LOAD

*,

Week(Date) as Week

Resident tmpCalendar;

DROP Table tmpCalendar;

Anonymous
Not applicable
Author

Anil,

Thanks for this, however my challenge is to create week intervals based off of a period of 84 days. So, essentially my data will only have week 1 to week 12 given the startDate and endDate.

Anonymous
Not applicable
Author

is it possible to replace the hard coded dates in the variables with my start and end date columns?

its_anandrjs

Yes it is possible but keep the name same and check the variable values also will it return [properly or not.

Anil_Babu_Samineni

Okay, Can you make sample and explain exact result set you want to see?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful