CEILING and FLOOR functions

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

CEILING and FLOOR functions

Post by 20 Ton Squirrel »

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' ] ;
War teaches us geography, getting old teaches us biology.
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: CEILING and FLOOR functions

Post by jim wood »

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.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
User avatar
20 Ton Squirrel
Posts: 71
Joined: Tue Jul 14, 2020 9:53 pm
OLAP Product: TM1
Version: Planning Analytics with Watson
Excel Version: Office 365
Location: Houston, TX

Re: CEILING and FLOOR functions

Post by 20 Ton Squirrel »

Sure! Scoot it on over there. Thanks, Jim! :)
War teaches us geography, getting old teaches us biology.
Post Reply