topic Re: Week 1 always jan 1 to jan 7/ All years in Connectivity & Data Prep

Week 1 always jan 1 to jan 7/ All years

srihari604 — Mon, 23 Dec 2024 16:19:33 GMT

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 !!

Re: Week 1 always jan 1 to jan 7/ All years

QFabian — Mon, 23 Dec 2024 17:43:30 GMT

Hi @srihari604 check broken_weeks parameter in week function:

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

Re: Week 1 always jan 1 to jan 7/ All years

Or — Mon, 23 Dec 2024 18:40:15 GMT

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?

Re: Week 1 always jan 1 to jan 7/ All years

nhenckel — Mon, 23 Dec 2024 18:43:56 GMT

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

Re: Week 1 always jan 1 to jan 7/ All years

srihari604 — Tue, 24 Dec 2024 06:56:37 GMT

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.

Re: Week 1 always jan 1 to jan 7/ All years

srihari604 — Tue, 24 Dec 2024 08:00:43 GMT

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.

Re: Week 1 always jan 1 to jan 7/ All years

Or — Tue, 24 Dec 2024 08:14:09 GMT

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

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

Ceil(daynumberofyear(Date)/7)

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

Re: Week 1 always jan 1 to jan 7/ All years

srihari604 — Tue, 24 Dec 2024 11:29:02 GMT

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.