Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
pra_kale
Creator III
Creator III

How to convert number of days into Month and Year

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.

1 Solution

Accepted Solutions
aarkay29
Specialist
Specialist

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

View solution in original post

18 Replies
Frank_Hartmann
Master II
Master II

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
Creator III
Creator III

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
Master II
Master II

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
Specialist
Specialist

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

View solution in original post

pra_kale
Creator III
Creator III

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:

QlikCommunity_Thread_251473_Pic1.JPG

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
Creator III
Creator III

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.