Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted
MVP & Luminary
MVP & Luminary

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

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