Find first day of week number

[ChristofZ]

Hi,

I have a Qlik-table with calendar weeks, and I'd like to display weekly values of the same week-number last year, so I need a function returning the dates for the same calendar week, start and end last year.

The answer is not

WeekStart(AddYears(myDate, -1))

because myDate could lie in a different calendar week last year compared to this year, and comparing calendar week 14, 2024 to calendar week 13, 2023 reads funny. 😬

What I would need are functions

WeekStart(week_number, year)

WeekEnd(week_number, year)

yielding start and end of week given week-number and the year. Thank you!

[TauseefKhan · Accepted solution]

Hi @ChristofZ,

In Qlik WeekStart() function does not allow directly entering a week number and a year without an actual date.

Follow the below Approach to get the Desired result:

Set FirstDateOfYear = MakeDate(Year);
Set StartFirstWeek = FirstDateOfYear - DayNumberOfYear(FirstDateOfYear) + 1 + If(WeekDay(FirstDateOfYear) > 0, 7 - WeekDay(FirstDateOfYear), 0);
Set StartOfWeek = StartFirstWeek + (WeekNumber - 1) * 7;
Set EndOfWeek = StartOfWeek + 6;

Let Year = 2023;
Let WeekNumber = 14;

Let FirstDateOfYear = MakeDate(Year);
Let StartFirstWeek = FirstDateOfYear - DayNumberOfYear(FirstDateOfYear) + 1 + If(WeekDay(FirstDateOfYear) > 0, 7 - WeekDay(FirstDateOfYear), 0);
Let StartOfWeek = StartFirstWeek + (WeekNumber - 1) * 7;
Let EndOfWeek = StartOfWeek + 6;

// Returns 'StartOfWeek' and 'EndOfWeek' for use in charts or tables
This script will give you the start and end dates of any specified week number in a particular year.

** When applicable please mark the correct/appropriate replies as "solution". Please LIKE threads if the provided solution is helpful to. **

[ChristofZ]

wow - thank you! 😎

Another solution would of course be to just add a corrective term

+ Week(myDate) - Week(AddYears(myDate, -1))) * 7

in the selection formula. It adds or subtracts 7 days, if last years week number is higher or lower than this years week number.

The start date of my selection is now:

WeekStart(AddYears(myDate,-1) + (Week(myDate) - Week(AddYears(myDate, -1))) * 7)

(Same for the week end date.)