topic Re: Problem with calculating months difference between 2 dates in QlikView

Problem with calculating months difference between 2 dates

triciagdaly — Thu, 10 Dec 2015 15:23:43 GMT

I am using the following expression to calculate the months difference between today and the EFFECTIVE_DATE, but when the two dates are within the same month, the expression is failing instead of returning 0. Any ideas?

year(today(2))*12)+month(today(2))) - (((year(EFFECTIVE_DATE)*12)+month(EFFECTIVE_DATE)))

Thanks much!

Re: Problem with calculating months difference between 2 dates

hemhund2016 — Thu, 10 Dec 2015 15:29:10 GMT

Hi,

It should work. I have tested it as below.

=((year(today(2))*12)+month(today(2))) - (((year(today()-3)*12)+month(today()-3)))

which is returning zero.

You may need to verify if the Effective date is being null or any other ?

Thanks,

Hemanth.

Re: Problem with calculating months difference between 2 dates

sunny_talwar — Thu, 10 Dec 2015 15:43:08 GMT

May be try this:

=RangeSum(((year(today(2))*12)+month(today(2))), -((year(EFFECTIVE_DATE)*12)+month(EFFECTIVE_DATE)))

Re: Problem with calculating months difference between 2 dates

triciagdaly — Thu, 10 Dec 2015 16:00:40 GMT

No, it's not returning the row with the two dates in the same month. Strange that I can separate this expression into two and then take a difference using fabs(end date-start date) and it works. But can't do in one expression.

This works but would rather have it in one expression and don't understand why it's not returning the row when it's in one expression.

end date:

year(today(2))*12)+month(today(2)))

start date:

year(EFFECTIVE_DATE)*12)+month(EFFECTIVE_DATE)))

month difference

[end date]-[start date]

Re: Problem with calculating months difference between 2 dates

triciagdaly — Thu, 10 Dec 2015 16:09:16 GMT

disregard the fabs() on the last reply.. do not need the absolute value.

Re: Problem with calculating months difference between 2 dates

Anonymous — Thu, 10 Dec 2015 16:20:49 GMT

Hello,

can't you use an expression like this one?:

=num((today() - EFECTIVE_DATE)/30, '0')

Re: Problem with calculating months difference between 2 dates

miikkaqlick — Thu, 10 Dec 2015 16:31:02 GMT

Hi!

Try this:

((year(today(2))*12)+month(today(2)))-

((year(End)*12)+month(End))

Result:

Br,

Miikka

Climber Finland

Re: Problem with calculating months difference between 2 dates

triciagdaly — Thu, 10 Dec 2015 16:51:38 GMT

Hemantha,

Where is the EFFECTIVE_DATE in your example?

Re: Problem with calculating months difference between 2 dates

triciagdaly — Thu, 10 Dec 2015 16:57:46 GMT

Jose,

This is rounding up always versus when it gets to midpoint in month.

Re: Problem with calculating months difference between 2 dates

effinty2112 — Thu, 10 Dec 2015 17:20:12 GMT

Hi Tricia,

Your expression had a lot of superfluous brackets that I've removed plus a couple that were incorrect that I've kept but remarked out to show where they were:

year(today(2))*12

// )

+month(today(2))

// )

- year(EFFECTIVE_DATE)*12+month(EFFECTIVE_DATE)

Anyway here's another expression to try (you can't have too many!)

=12*(Year(today(2)) - Year(EFFECTIVE_DATE)) + Month(today(2)) - Month(EFFECTIVE_DATE))

Re: Problem with calculating months difference between 2 dates

triciagdaly — Thu, 10 Dec 2015 19:06:06 GMT

All,

Thank you all for your input. The only thing that seems to work is to put the original expression in the load script. Not sure why the expression will not work, but I surrender to the quirks of qlikview