Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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
Author

Hi,

Use Interval()

as

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

and

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

Not applicable
Author

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

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

Not applicable
Author

any other suggestion?

Not applicable
Author

Hi,

use

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

Regards,

Shubhu

Not applicable
Author

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