Week 1 always jan 1 to jan 7/ All years

[srihari604]

Hello,

I am trying to prepare a dataset and compare various parameters across years.

But , I need to have every year data arranged such that, Week 1 is always Jan 1 to Jan 7 (Irrespective of day of week).

2017 - Week 1 - jan 1 to jan 7 and so on

2018 - Week 1 - jan 1 to jan 7 and so on.

Any suggestions on this are helpful !!

Thanks in Advance !!

[QFabian]

Hi @srihari604 check broken_weeks parameter  in week function:

https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/DateAnd...

[Or]

Actually, this is a case that even parameterizing the functions won't solve, I think? It also won't fit any week numbering system I've ever seen used, but that's none of my business...

Should be resolvable using something like Floor((daynumberofyear(Date)-1)/7) as WeirdWeekNumber?

[nhenckel · Accepted solution]

If you are looking to define a custom weekly structure where Week 1 always contains January 1 to January 7 (irrespective of the day of the week) for every year, the following Qlik Sense script should do it.

This script generates a calendar where each week is strictly defined as a block of 7 days, starting with Week 1 as January 1–7, Week 2 as January 8–14, and so forth. This structure does not align with the "real" calendar week numbering (ISO or otherwise) but provides the custom setup you requested.

Script for Custom Weekly Calendar

// Set the start and end years manually
LET vStartYear = 2024;
LET vEndYear = 2025;

FOR vYear = $(vStartYear) TO $(vEndYear)

// Define start and end dates for the current year
LET vYearStartDate = MakeDate('$(vYear)', 1, 1);
LET vYearEndDate = MakeDate('$(vYear)', 12, 31);

// Generate daily calendar for the current year with reset week numbers
Calendar:
LOAD
    Date('$(vYearStartDate)' + (IterNo() - 1)) AS CalendarDate,
    Year(Date('$(vYearStartDate)' + (IterNo() - 1))) AS Year,
    Month(Date('$(vYearStartDate)' + (IterNo() - 1))) AS Month,
    Day(Date('$(vYearStartDate)' + (IterNo() - 1))) AS Day,
    Ceil((IterNo()) / 7) AS WeekNumber, // Custom week number (every 7 days)
    WeekDay(Date('$(vYearStartDate)' + (IterNo() - 1))) AS WeekDay
AUTOGENERATE 1
WHILE '$(vYearStartDate)' + (IterNo() - 1) <= '$(vYearEndDate)';

NEXT

Example Output

CalendarDate	Year	Month	Day	WeekNumber	WeekDay
2024-01-01	2024	1	1	1	Monday
2024-01-02	2024	1	2	1	Tuesday
2024-01-07	2024	1	7	1	Sunday
2024-01-08	2024	1	8	2	Monday
...	...	...	...	...	...
2025-01-01	2025	1	1	1	Wednesday
2025-01-07	2025	1	7	1	Tuesday
2025-01-08	2025	1	8	2	Wednesday

[srihari604]

Thanks @QFabian ,

But this wouldn't give full weeks in some cases.

I was looking for something to have Jan 1 to Jan 7 as Week 1 irrespective of what day Jan 1 is and this is to be followed for the data from 2012 to 2024 and future.

[srihari604]

Thanks @Or ,

Nice Alias name though.

This probably needs some tweaking , 1st week starts with 0 and also , the last week always has 2 days which is not always correct.

[Or]

Oops. Minor tweaking. My bad. I always do this when I'm working with math functions and dates.

Floor((daynumberofyear(Date)-0.99)/7)

or

Ceil(daynumberofyear(Date)/7)

The latter obviously makes more sense. I whiffed on the direction here.

[srihari604]

Thanks @nhenckel for the detailed explanation!!

That worked for me , I will go ahead and figure out how to incorporate these new fields into my expressions and data model.