Time formats in cube views

[schmrob]

Hi All,

I have some decimal hour values that I want to be able to display in hh:mm format.

This works ok for values < 24 hours, but I'd like the aggregated numbers to display the same as Excel's [hh]:mm number format e.g. 1,234.75 hours in decimal hours should display as 1234:45 in hh:mm.

Is this possible?

[Wim Gielis]

Hello,

What kind of cube views - any client ? PAW, TM1 Architect, TM1 Web, PAfE, …

[schmrob]

Hi Wim,

In general, all of them, but specifically PAW and/or TM1Web.

[MarenC]

Hi,

Could you create a string measure and construct it by taking the 1234 and combining it with the colon and the converted decimal part?
Then use this in PAW for display purposes. No idea how this would look but only thing I can think of.

Maren

[Wim Gielis]

Me too, I don't have a better idea, sorry.

[schmrob]

Thanks for the suggestions Maren + Wim,

Unfortunately I think you're right in that it can't be done with native number formats

Rob

[gtonkin]

Hi Rob,
Been lurking in the shadows watching the responses on this as I have not had time to fiddle previously.

The main issue here is that you are not dealing with a serial number that can be formatted.
If you divide your 1234.75 by 24 and apply a format of hh:mm to the result you end up with something like 10:45 but miss the other 19 days as the formatting would now want to convert the serial number to a proper date/time.

What I did was create a leaf and a C level element, added the leaf with a weighting of 1/24 and added a Format of b:hh:mm to the C level.

DateTime.png (3.58 KiB) Viewed 1238 times

If you change the format to b:d hh:mm you then get the 19 days.
Not particularly useful but there you have it.

Part of the reason for the reply was also to share the link to the ICU formats can be used in PAW.

[schmrob]

The main issue here is that you are not dealing with a serial number that can be formatted.
If you divide your 1234.75 by 24 and apply a format of hh:mm to the result you end up with something like 10:45 but miss the other 19 days as the formatting would now want to convert the serial number to a proper date/time.

What I did was create a leaf and a C level element, added the leaf with a weighting of 1/24 and added a Format of b:hh:mm to the C level.

If you change the format to b:d hh:mm you then get the 19 days.
Not particularly useful but there you have it.

This is an interesting point: I think it treats the number as a serial date when formatting it anyway:
1234.75 / 24 = 51.44792 days, so I'm guessing that the '20' that the formatter returns for the 'd' value (my calc picked up 20 vs your 19) is in fact a Feb date: 51 days = 31 days in Jan + (51-31) = 20 days in Feb; the remaining 0.44792 days = 10h:45min

Anyway, thanks all for the replies, and I'm glad this one isn't a specific requirement!