How to convert number of days into Month and Year

[pra_kale]

Hi,

How i can convert number of days coming through 2 date differences into number of months and year.

I want number of months like 15,28,60 that way.

Please help in resolving the issue.

Thanks in advance.

[Frank_Hartmann]

im not sure if i understood your requirement.

Try to create a straight table with policynumber and startdate as dimension

and then add this expression:

date(num(date(start_date,'DD-MM-YYYY'))+num(Number_of_days))

hope this helps

[pra_kale]

Hi,

I actually want to convert the difference of number of days between  (today - Start_date) into number of Months as well as Year.

like Date difference between 1-Mar-2003 to 27-Feb-2017 is 5112 days. I want out-put to be convert into number of Months and Year.

[sunny_talwar]

This would be based on 365 days in an year? I mean what would 5112 correspond to outside of QlikView?

[Frank_Hartmann]

dim:

-policynumber

-startdate

exp:

-num(Number_of_days)

-num(subfield(num(Number_of_days)/365,'.',1))      for years

-floor(num(0.&subfield(num(Number_of_days)/365,'.',2))*365)        for days

hope this helps

[aarkay29 · Accepted solution]

May be this:

(year(today())*12+month(today()))-(year(start_date)*12+month(start_date))  as NumofMonths

and

((year(today())*12+month(today()))-(year(start_date)*12+month(start_date)) )/12 as NumofYears

[pra_kale]

Hi Sunny,

Whether in Qlik we have any formula which will convert the date difference depending upon 365 or 366 days in a Year.

So I will get the accurate out-put rather than dividing by 30 or 31 days.

same way for to know the year rather to divide by 365 / 366 days.

[Peter_Cammaert]

Using your own example dates, what would you like to get for 27-Feb-2017 vs 1-Mar-2003 (in Days, Months, Years)?

And for 7-Jan-2016 vs 7-Dec-2015?

[MarcoWedel]

Hi,

maybe one solution might be:

tabPolicies:
LOAD *,
    Div(AgeMonthsTotal,12) as AgeYears,
    Mod(AgeMonthsTotal,12) as AgeMonths;
LOAD Policynumber,
    start_date,
    Month(Today())-Month(start_date)+(Year(Today())-Year(start_date))*12 as AgeMonthsTotal
FROM [https://community.qlik.com/servlet/JiveServlet/download/1221326-267639/No_of_days.xlsx] (ooxml, embedded labels, table is Sheet1);

hope this helps

regards

Marco

[pra_kale]

Hi Aar Kay,

Thanks all for your support..

It is working but only thing is if Months or Year are coming like 12.5 and above then that month or Year should rounding up to 13.