topic find month & days between 2 date in QlikView

find month & days between 2 date

Tue, 10 May 2011 07:52:00 GMT

hello all.

I need to find month & days between 2 dates.

Any suggestion?

Best regards

Muncho

Re: find month & days between 2 date

Tue, 10 May 2011 08:13:40 GMT

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

Re: find month & days between 2 date

Tue, 10 May 2011 08:24:23 GMT

Hi,

Use Interval()

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

and

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

Re: find month & days between 2 date

Tue, 10 May 2011 08:34:42 GMT

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

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

Re: find month & days between 2 date

Tue, 10 May 2011 10:58:28 GMT

any other suggestion?

Re: find month & days between 2 date

Tue, 10 May 2011 11:46:41 GMT

Hi,

use

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

Regards,

Shubhu

Re: find month & days between 2 date

Tue, 10 May 2011 13:46:56 GMT

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