topic Re: Months Between dates in App Development

Months Between dates

gidon500 — Wed, 12 Aug 2015 05:42:22 GMT

Hi guys

i need to calculate number of months between date

from:15/01/2014

to :12/08/2015

any function to do it

thanks

gidon

Re: Months Between dates

Wed, 12 Aug 2015 05:49:54 GMT

Hi David

using month() function you get the monthname of any particular date as ( Jan,Feb,Mar .. first 3 characters of month as output) . take a map table with the monthname and monthnumber values and use applymap function to map the month name with month number

Hope this solution helps you

Regards

Chitra

Re: Months Between dates

Wed, 12 Aug 2015 06:36:07 GMT

Hi,

Try this.

I have attached QVW.

Re: Months Between dates

Wed, 12 Aug 2015 08:19:54 GMT

Extract the month in number format and the year from both dates and convert the date to a YearMonth sequential number so you can operate between them:

difference = ( year(date2) * 12 100 ) + num(month(date2)) - ( year(date1) * 12 100 ) + num(month(date1))

Edit: I'm sorry I am switching between many tabs and replied so fast I dind't realize I wrote 100 instead of 12).

Bad habit!

Re: Months Between dates

maxgro — Wed, 12 Aug 2015 08:31:52 GMT

(year('12/08/2015')*12 + month('12/08/2015'))

(year('15/01/2014')*12 + month('15/01/2014'))

Re: Months Between dates

ecolomer — Wed, 12 Aug 2015 08:33:30 GMT

Borja,

Efectivamente, multiplicando por 100 el año y sumando el mes obtienes AñoMes, pero al restar si hay mas de un año de diferencia, el resultado es erróneo. Pruebalo y verás.

Para restar de forma directa deberías multiplicar por 12, entonces si que la resta produce el número de meses.

Saludos,

Enrique Colomer

Re: Months Between dates

Wed, 12 Aug 2015 08:37:46 GMT

Así es. El problema es que varias veces he usado el método de * 100 para usar meses secuenciales para los ejes de un gráfico cuando quieres ver meses de varios años, y funciona perfectamente.

Efectivamente, a la hora de operar entre ellos, ha de ser *12 para que la secuencia de meses sea completa y puedas restar cualquier mes.

Has estado rápido!

Re: Months Between dates

Wed, 12 Aug 2015 10:50:48 GMT

Hi,

Please find the attached QVW.

Re: Months Between dates

qlikviewwizard — Thu, 13 Aug 2015 10:49:46 GMT

‌hi did you find the solution?