Showing an interval in Y M D hh:mm:ss format

[HeshamKhja]

Hey,

I need to show time period in the format of 'Year Month Day hh:mm:ss'. Qlik Sense has the 'D hh:mm:ss' format, but the numbers I have are big and days level aren't enough to comprehend them.

The field I am trying to format is a number in unit of days. Example: 18276.2786 means 18276 days and the decimals are the hours.

I searched the community but couldn't find an answer. Please advice.

Note: I tried to do it manually by dividing by 365 for year and the remainder by 30 for months then the remainder will be days using the Mod() function. But I saw a mistake in the numbers. I have two fields and the third as their addition:

	[Idle Working Hours]	[Moving Working hours]	[Total Working Hours] ([Idle]+[Moving])
Their sum is	49,868	157,271	207,139
When manually formatted	136 Years 7 Months 18 Days	430 Years 10 Months 21 Days	567 Year 6 Months 4 Days

If you check the numbers, you will see all were formatted correctly from the number. However, the formatted total is missing 5 days (18 + 21 =39, 1Month 9 Days, while the formatted total shows 4 days) . So how come they add correctly in numbers, and they are formatted correctly, yet the formatted total has 5 days missing?

Regards,

[stevedark]

Hi @HeshamKhja 

This is going to take a nifty bit of nesting in brackets and removing of remainders, but here are some starting points.

To get the difference between two months, in months, you can do the following:

((Year(Date1)*12)+Month(Date1))-((Year(Date2)*12)+Month(Date2))

The result of that divided by 12 gives you the years, the mod of it will be the months.

Days will then need to be calculated using:

Day(Date1) - Day(Date2)

This will obviously be either a positive or negative number. If it's positive then happy days. If negative you will need to take one of the number of months you have (which could actually knock a year off if you have x Years and zero months) and you will need to find the number of days using:

Day(MonthEnd(Date2)) + (Day(Date1) - Day(Date2))

The overall expression will require some inline IF statements and careful checking with some test data, but hopefully those thoughts move you a little further forward.

Actually, you may get accurate enough with your previous expressions if you just divide by 365.25 (which spreads each leap day evenly across four years) but it's not taking into account where leap days are falling.

Cheers,

Steve

[HeshamKhja]

Thank you for your reply Mr. Steve. I truly appreciate your effort.

Very helpful information and I'll for sure use this knowledge in future projects.

Your reply is one way to solve the first part of the question, but do you know why I have 5 days missing by the method I used?

Thanks again!

[stevedark]

Hi @HeshamKhja 

I suspect that it is the fact that you are not taking into account that years are actually 365.25 days long (apportioning leap days across years) and not all months are 30 days. On average they are 30.4375 days long, using those numbers for months and years will give you more accurate results over long periods, but over short periods they will be less useful.

Hope that helps.

Steve