Counting number of weeks between two date fields which are not from the same year

lessassy — Sat, 16 Nov 2024 20:41:24 GMT

Hello Qlik COmmunity

I have two date field formatted this way 'DD/MM/YYYY'

I would like to count the number of weeks between this twho dates knowing that date 1 is YYYY and date 2 is YYYY + n

I use this formula in the expression:

=if(Num(Year([Delivery])) = Num(Year([Schedule])),
Num(Week([Delivery])) - Num(Week([Schedule])),
if(Num(Year([Delivery])) > Num(Year([Schedule])),
52 - Num(Week([Schedule])) + Num(Week([Delivery]))))

The formula is working but the issue is the number 52. It represents the number of week in a year.

But the issue is i d'like to turn this into an expression or variable which tell for each year the number of weeks.

Besides the issue with my formula is that it works only if the date fields are only a year appart.

Thanks for the help

Re: Counting number of weeks between two date fields which are not from the same year

marcus_sommer — Wed, 12 Jun 2019 12:34:12 GMT

I think you could use a quite simple approach like:

floor((date1 - date2) / 7)

and another method might be:

(year(date1) * 52 + week(date1)) - (year(date2) * 52 + week(date2))

Depending to your expected results you might need a few additionally measurements to adjust the rounding-direction or to handle the week 53 and/or shift-years or something similar.

- Marcus

topic Counting number of weeks between two date fields which are not from the same year in QlikView

Counting number of weeks between two date fields which are not from the same year

Re: Counting number of weeks between two date fields which are not from the same year