TM1 rule using DB IF and Stet

Post Reply
Mark RMBC
Regular Participant
Posts: 163
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

TM1 rule using DB IF and Stet

Post by Mark RMBC » Fri Oct 19, 2018 2:21 pm

Hi,

Am looking at making a TM1 model (which calculates housing rents) a little more efficient, just looking at the rules it raised my curiosity which is the best way to write a rule,

Option 1

Code: Select all

['Formula Rent Calc A']=N:

if(NUMBR(SUBST(!Year,1,4)) > NUMBR(SUBST(!UH_Versions,1,4)),

DB('UH_Current_Year_Account_Values',!Year,!UH_Versions,!UH_House_Acc,!UH_Debit_Type,'Bed Weighting') *

DB('UH_Debit_Code_Assumptions',!Year,!UH_Versions,!UH_Debit_Type,'National Average Rent') *

DB('UH_Debit_Code_Assumptions',!Year,!UH_Versions,!UH_Debit_Type,'Relative County Manual Earnings'),STET);
Option 2

Code: Select all

['Formula Rent Calc A']=N:


DB(if(NUMBR(SUBST(!Year,1,4)) > NUMBR(SUBST(!UH_Versions,1,4)),'UH_Current_Year_Account_Values', Stet), !Year,!UH_Versions,!UH_House_Acc,!UH_Debit_Type,'Bed Weighting')

*

DB(if(NUMBR(SUBST(!Year,1,4)) > NUMBR(SUBST(!UH_Versions,1,4)),'UH_Debit_Code_Assumptions', Stet), !Year,!UH_Versions,!UH_Debit_Type,'National Average Rent')

*

DB(if(NUMBR(SUBST(!Year,1,4)) > NUMBR(SUBST(!UH_Versions,1,4)),'UH_Debit_Code_Assumptions', Stet), !Year,!UH_Versions,!UH_Debit_Type,'Relative County Manual Earnings');

The rule is currently as per option 1 but was thinking of changing it to option 2, I doubt it will make little difference but am interested what might be considered best practice

cheers, Mark

Wim Gielis
MVP
Posts: 1826
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: TM1 rule using DB IF and Stet

Post by Wim Gielis » Fri Oct 19, 2018 2:30 pm

No, go for option 1. Option 2 makes very little sense actually.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Mark RMBC
Regular Participant
Posts: 163
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: TM1 rule using DB IF and Stet

Post by Mark RMBC » Fri Oct 19, 2018 2:31 pm

Ok thanks Wim

bgregs
Posts: 37
Joined: Wed Sep 12, 2018 11:19 am
OLAP Product: TM1 / Planning Analytics
Version: 2.0
Excel Version: 2016

Re: TM1 rule using DB IF and Stet

Post by bgregs » Fri Oct 19, 2018 2:51 pm

This goes for most languages (mostly c based, but will apply here as well), try to avoid extra conditionals when possible. Depending on the case, this is sometimes unavoidable, but in your situation the extra calls in option 2 would be considered more "expensive" than the first (i.e. more "cycles" to check unnecessary conditions). Now, this isn't a resource constrained instance where we are trying to squeeze the most out of scarce memory, so always default to readability. Option 1 is much cleaner, and the guy who comes after you will greatly appreciate only having to read that conditional check once. ;)

Post Reply