Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

find month & days between 2 date

hello all.

I need to find month & days between 2 dates.

Any suggestion?

Best regards

Muncho

6 Replies
Not applicable

Re: find month & days between 2 date

Well i tried like this:

div(date2-date1, 30) as months

(date2-date1)-div(date2-date1, 30)*30 as days

this is give me some months & days.

But i like to know is there any other better solution of this?

regards

Not applicable

Re: find month & days between 2 date

Hi,

Use Interval()

as

Interval ( date2 - date1, 'mm' ) for months

and

Interval ( date2 - date1, 'dd' ) for days

Not applicable

Re: find month & days between 2 date

don't know, it's not giving me right result.

interval(date2-date1.'mm')  - it's giving me just days.

Not applicable

Re: find month & days between 2 date

any other suggestion?

Not applicable

Re: find month & days between 2 date

Hi,

use

SubField(Interval(date2 - date1, 'mm-dd'), '-', 1)

Regards,

Shubhu

Not applicable

Re: find month & days between 2 date

Hi,

I suggest to split this problem into two steps:

1. Create a calendar

2. Count days and months based on the load statement, like this:

LOAD

     COUNT(DATE)     AS  DAYS,

     COUNT(DISTINCT MONTH)     AS MONTHS

RESIDENT Calendar

WHERE

          (DATE >= '$(dateStart)') AND

           (DATE <= '$(dateEnd)')

;

"dateStart","dateEnd" - variables.

This will give you exactly what you need.

Kind regards,

JTPro