Calculate weeks and days in QlikView

[Anonymous]

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.

[Anonymous]

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

[#3]

Hey Bradley,

Attached with answer.

Thanks

AJ

[rbecher · Accepted solution]

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

[MK_QSL]

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]

Thank you Ralf, just what I was looking for.