Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
Showing results for 
Search instead for 
Did you mean: 

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

Hello Qlik COmmunity


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

Comparaison Date.png


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

Labels (3)
1 Reply
MVP & Luminary
MVP & Luminary

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