CEILING and FLOOR functions

Ideas and tips for enhancing your TM1 application
Post Reply
20 Ton Squirrel
Posts: 19
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: 11.7.00002.1
Excel Version: Office 365

CEILING and FLOOR functions

Post by 20 Ton Squirrel » Fri Feb 05, 2021 3:29 pm

TM1 lacks the CEILING and FLOOR functions but I recently set about emulating them formulaically. I hope posting this here helps someone in the Distant Dark Future™

Code: Select all

[ 'Ceiling' ] = N: 
    ( INT ( [ 'Value' ] \ [ 'Significance' ] ) 
     - IF ( [ 'Value' ] \ [ 'Significance' ] - INT ( [ 'Value' ] \ [ 'Significance' ] ) > 0 
           , -1
           , 0
          ) 
    ) * [ 'Significance' ] ;
    
[ 'Floor' ] = N:  
    INT ( [ 'Value' ] \ [ 'Significance' ] ) * [ 'Significance' ] ;
TM1 Version: 11.8.00200.24

User avatar
jim wood
Site Admin
Posts: 3874
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: TM1 10.2.2
Excel Version: 2007
Location: 1639 Route 10, Suite 107, Parsippany, NJ, USA
Contact:

Re: CEILING and FLOOR functions

Post by jim wood » Fri Feb 05, 2021 4:54 pm

You should put this in the useful code section of the site. It's very useful and in the main forum it will get moved down quickly. Let me know if you'd like it moved over there?

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Application Consulting Group (ACG) TM1 Consulting
OS: Windows 10 64-bit. TM1 Version: 10.2.2

20 Ton Squirrel
Posts: 19
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: 11.7.00002.1
Excel Version: Office 365

Re: CEILING and FLOOR functions

Post by 20 Ton Squirrel » Fri Feb 05, 2021 5:41 pm

Sure! Scoot it on over there. Thanks, Jim! :)
TM1 Version: 11.8.00200.24

Post Reply