Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

gidon500
Contributor II

Months Between dates

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

8 Replies
Not applicable

Re: Months Between dates

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

Not applicable

Re: Months Between dates

Hi,

Try this.

I have attached QVW.

Not applicable

Re: Months Between dates

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!

MVP
MVP

Re: Months Between dates

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

-

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

ecolomer
Honored Contributor II

Re: Months Between dates

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

Not applicable

Re: Months Between dates

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!

Not applicable

Re: Months Between dates

Hi,

Please find the attached QVW.

Arjunarao
Honored Contributor II

Re: Months Between dates

‌hi did you find the solution?