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?
Time formats in cube views
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Time formats in cube views
Hello,
What kind of cube views - any client ? PAW, TM1 Architect, TM1 Web, PAfE, …
What kind of cube views - any client ? PAW, TM1 Architect, TM1 Web, PAfE, …
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 6
- Joined: Fri Oct 23, 2020 2:16 am
- OLAP Product: TM1
- Version: PA 2.0.x
- Excel Version: 365
Re: Time formats in cube views
Hi Wim,
In general, all of them, but specifically PAW and/or TM1Web.
In general, all of them, but specifically PAW and/or TM1Web.
-
- Regular Participant
- Posts: 350
- Joined: Sat Jun 08, 2019 9:55 am
- OLAP Product: Planning Analytics
- Version: Planning Analytics 2.0
- Excel Version: Excel 2016
Re: Time formats in cube views
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
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
-
- MVP
- Posts: 3113
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Time formats in cube views
Me too, I don't have a better idea, sorry.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 6
- Joined: Fri Oct 23, 2020 2:16 am
- OLAP Product: TM1
- Version: PA 2.0.x
- Excel Version: 365
Re: Time formats in cube views
Thanks for the suggestions Maren + Wim,
Unfortunately I think you're right in that it can't be done with native number formats
Rob
Unfortunately I think you're right in that it can't be done with native number formats
Rob
- gtonkin
- MVP
- Posts: 1199
- Joined: Thu May 06, 2010 3:03 pm
- OLAP Product: TM1
- Version: Latest and greatest
- Excel Version: Office 365 64-bit
- Location: JHB, South Africa
- Contact:
Re: Time formats in cube views
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. 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.
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. 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.
-
- Posts: 6
- Joined: Fri Oct 23, 2020 2:16 am
- OLAP Product: TM1
- Version: PA 2.0.x
- Excel Version: 365
Re: Time formats in cube views
This is an interesting point: I think it treats the number as a serial date when formatting it anyway:gtonkin wrote: ↑Tue Mar 08, 2022 11:32 am
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.
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!