topic Re: How to convert number of days into Month and Year in QlikView

How to convert number of days into Month and Year

pra_kale — Mon, 27 Feb 2017 15:48:42 GMT

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.

Re: How to convert number of days into Month and Year

Frank_Hartmann — Mon, 27 Feb 2017 16:02:05 GMT

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

Re: How to convert number of days into Month and Year

pra_kale — Mon, 27 Feb 2017 16:11:51 GMT

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.

Re: How to convert number of days into Month and Year

sunny_talwar — Mon, 27 Feb 2017 16:17:56 GMT

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

Re: How to convert number of days into Month and Year

Frank_Hartmann — Mon, 27 Feb 2017 16:27:59 GMT

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

Re: How to convert number of days into Month and Year

aarkay29 — Mon, 27 Feb 2017 16:28:38 GMT

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

Re: How to convert number of days into Month and Year

pra_kale — Mon, 27 Feb 2017 16:31:55 GMT

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.

Re: How to convert number of days into Month and Year

Peter_Cammaert — Mon, 27 Feb 2017 17:11:14 GMT

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?

Re: How to convert number of days into Month and Year

MarcoWedel — Mon, 27 Feb 2017 23:23:41 GMT

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

Re: How to convert number of days into Month and Year

pra_kale — Tue, 28 Feb 2017 15:26:42 GMT

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.

Re: How to convert number of days into Month and Year

aarkay29 — Tue, 28 Feb 2017 15:35:25 GMT

do you want to round off the year 12.5 to 13

how do you want to see it??

Re: How to convert number of days into Month and Year

pra_kale — Tue, 28 Feb 2017 16:12:21 GMT

Yes I want to round it off to 13 if months or year are coming to 12.5 , 12.6 or 12.7 then it should round it off to 13.

Re: How to convert number of days into Month and Year

Anil_Babu_Samineni — Tue, 28 Feb 2017 16:15:16 GMT

Then Use Round()

Round(Expression) -- It will change to Constant value

Re: How to convert number of days into Month and Year

pra_kale — Tue, 28 Feb 2017 16:32:07 GMT

Yes Thanks Anil I have used this function but it is converting only Year and not months..

I think expression used for to derived months is not giving out-put in decimal..so it is not rounding off.

I have attached the application..

Re: How to convert number of days into Month and Year

Anil_Babu_Samineni — Tue, 28 Feb 2017 16:37:23 GMT

Is this you are expecting to see in UI

Re: How to convert number of days into Month and Year

pra_kale — Tue, 28 Feb 2017 17:17:12 GMT

Hi,

Yes but for Month expression as well..I think It is working only for Year expression.

If you see Month out-put it is not giving any decimal so i am not understanding whether it is rounding it off or not.

Re: How to convert number of days into Month and Year

aarkay29 — Tue, 28 Feb 2017 17:24:34 GMT

Prasad,

Num of months are months between the dates so they are Intergers i.e they do not have any decimals.

since we are dividing Num of months by 12 we are getting decimals for years which can be rounded of using ROUND function.

Re: How to convert number of days into Month and Year

pra_kale — Wed, 01 Mar 2017 01:58:02 GMT

Thanks Aar Kay,

But, weather it is possible i will get a number of months also in a decimal which i will Rounding off later on by Round function.

The reason i require this field also in decimal only because I have a grid in which I have a Values in front of Months. like for 13 months 12000, 14 Months 25000.....36 months 100000 and so on..

So i have to consider the value of a Next month if my Months are 13.5 or more..then i will consider the value of 14 Months and not of 13 months..

Re: How to convert number of days into Month and Year

pra_kale — Wed, 01 Mar 2017 17:29:50 GMT

Hi,

Weather it is possible to get the month out_put in decimal, post which i will convert by Round off function..