Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Calculate weeks and days in QlikView

I have two dates and I need a formula to subtract the start date from end date and get back the number of weeks and days that are between the two dates. Ex: 3/10/14 - 3/1/14 = 1 week and 3 days.

1 Solution

Accepted Solutions
rbecher
MVP
MVP

Hi Bradley,

this should work:

=floor((date#('3/10/14') - date#('3/1/14')) / 7) & ' Week(s) ' & mod((date#('3/10/14') - date#('3/1/14')) +1, 7) & ' Day(s)'

or with fields:

=floor((EndDate - StartDate) / 7) & ' Week(s) ' & mod((EndDate - StartDate) +1, 7) & ' Day(s)'

- Ralf

Astrato.io Head of R&D

View solution in original post

5 Replies
Anonymous
Not applicable
Author

You need to understand the fundamentals of Dates in QV before you tackle these calcs.

Here is an age old but always valid 1 pager which is mandatory.....

Primer for QlikView Date fields

Not applicable
Author

Hey Bradley,

Attached with answer.

Thanks

AJ

rbecher
MVP
MVP

Hi Bradley,

this should work:

=floor((date#('3/10/14') - date#('3/1/14')) / 7) & ' Week(s) ' & mod((date#('3/10/14') - date#('3/1/14')) +1, 7) & ' Day(s)'

or with fields:

=floor((EndDate - StartDate) / 7) & ' Week(s) ' & mod((EndDate - StartDate) +1, 7) & ' Day(s)'

- Ralf

Astrato.io Head of R&D
MK_QSL
MVP
MVP

Ajay has given good answer on Script side.

UI side can be done by

Where End = EndDateField and Start = StartDateField

=Floor(Interval(End-Start,'dd')/7) &' Weeks ' & Mod(Interval(End-Start+1,'dd'),7)& ' Days'

Anonymous
Not applicable
Author

Thank you Ralf, just what I was looking for.