can anyone suggest approach to write TI/ Rule for implementing interpolation in TM1

 Posts: 5
 Joined: Mon Feb 04, 2019 12:38 am
 OLAP Product: TM1
 Version: 10.2.2
 Excel Version: 2016
can anyone suggest approach to write TI/ Rule for implementing interpolation in TM1
i need to interpolate the MAC rate in the attached cube :
MAC rate should be interpolated based on Percentage change in Max Retail price (RRP) and the Existing MAC rate at that point of time
Scenario :
MRC discount % and Monthly fee RRP will be loaded from another cube .
We are calculating MAC Rate = Monthly RRP (1 MRC discount %) if discount available .
for a particular access type and Quote :
Now we need to populate the MAC rate for remaining bandwidths where discount not available .
1. Scan the values by ascending order of bandwidth
2. Take the first available existing MAC Rate ( lets say MAC rate available 540.54 In the above example )
• Populate the Mac Rate for all other bandwidths less than that bandwidth as below :
MAC Rate =MAC Rate available (1 % Change in Monthly RRP )
Example :
For 4 M
% Change in Monthly fee RRP ( while calculating Mac for 4M bandwidth ) = (Monthly fee (5M)  Monthly fee (4M) ) / Monthly fee (5M)
MAC available = MAC for 5M
For 3 M
% Change in Monthly fee RRP ( while calculating Mac for 3M bandwidth ) = (Monthly fee (4M)  Monthly fee (3M) ) / Monthly fee (4M)
MAC avaialble = MAC for 4M
Please share your thoughts
Thanks ,
val
MAC rate should be interpolated based on Percentage change in Max Retail price (RRP) and the Existing MAC rate at that point of time
Scenario :
MRC discount % and Monthly fee RRP will be loaded from another cube .
We are calculating MAC Rate = Monthly RRP (1 MRC discount %) if discount available .
for a particular access type and Quote :
Now we need to populate the MAC rate for remaining bandwidths where discount not available .
1. Scan the values by ascending order of bandwidth
2. Take the first available existing MAC Rate ( lets say MAC rate available 540.54 In the above example )
• Populate the Mac Rate for all other bandwidths less than that bandwidth as below :
MAC Rate =MAC Rate available (1 % Change in Monthly RRP )
Example :
For 4 M
% Change in Monthly fee RRP ( while calculating Mac for 4M bandwidth ) = (Monthly fee (5M)  Monthly fee (4M) ) / Monthly fee (5M)
MAC available = MAC for 5M
For 3 M
% Change in Monthly fee RRP ( while calculating Mac for 3M bandwidth ) = (Monthly fee (4M)  Monthly fee (3M) ) / Monthly fee (4M)
MAC avaialble = MAC for 4M
Please share your thoughts
Thanks ,
val
 Attachments

 example_Prot.docx
 (72.66 KiB) Downloaded 13 times
Last edited by valletchuman on Wed Feb 06, 2019 12:03 am, edited 1 time in total.
 paulsimon
 MVP
 Posts: 670
 Joined: Sat Sep 03, 2011 11:10 pm
 OLAP Product: TM1
 Version: PA 2.0.5
 Excel Version: 2016
 Contact:
Re: can anyone suggest approch to write TI/ Rule for implementing interpolation in TM1
Hi Val
You could do this with rules  take a look at the Rules Guide  for Stock movements. Your problem is similar to that.
You could also do it with TI. All you need is a WHILE loop
Regards
Paul Simon
You could do this with rules  take a look at the Rules Guide  for Stock movements. Your problem is similar to that.
You could also do it with TI. All you need is a WHILE loop
Regards
Paul Simon

 Posts: 5
 Joined: Mon Feb 04, 2019 12:38 am
 OLAP Product: TM1
 Version: 10.2.2
 Excel Version: 2016
Re: can anyone suggest approch to write TI/ Rule for implementing interpolation in TM1
Hi Paul ,
Thanks for replay . Please find below constraints and sample model for further understanding .i dint write the above question properly . May be attached model give you proper understanding on the problem .
Constraints :
1. MAC Rate should be consistent with respect to bandwidth
2. MAC Rate for the Approved discounted Bandwidths shouldn't be changed
3. Users should be able to change the discounts for the interpolated MAC Rate and then adjust accordingly
4. Standard cost is also available for each bandwidth and access type
5. Min discount 0% and Max discount can be upto 100%
6. each Quote might have availed any bandwidths previously . ( it can be all lesser bandwidths , some random bandwidths, or few higher bandwidths
Discount scenarios :
1. Existing rates are roughly similar (in terms of % discount / markup) within each access type
2. Existing rates are roughly similar (in terms of % discount / markup) within each access type but with some outliers
3. Wildly varied discount % across bandwidths within Access type
i split the calculation to different parts .
sample excel model i think of attached in this email.
Looking forward for your insights / thoughts to implement this in TM 1 .
Thanks ,
Thanks for replay . Please find below constraints and sample model for further understanding .i dint write the above question properly . May be attached model give you proper understanding on the problem .
Constraints :
1. MAC Rate should be consistent with respect to bandwidth
2. MAC Rate for the Approved discounted Bandwidths shouldn't be changed
3. Users should be able to change the discounts for the interpolated MAC Rate and then adjust accordingly
4. Standard cost is also available for each bandwidth and access type
5. Min discount 0% and Max discount can be upto 100%
6. each Quote might have availed any bandwidths previously . ( it can be all lesser bandwidths , some random bandwidths, or few higher bandwidths
Discount scenarios :
1. Existing rates are roughly similar (in terms of % discount / markup) within each access type
2. Existing rates are roughly similar (in terms of % discount / markup) within each access type but with some outliers
3. Wildly varied discount % across bandwidths within Access type
i split the calculation to different parts .
sample excel model i think of attached in this email.
Looking forward for your insights / thoughts to implement this in TM 1 .
Thanks ,

 Posts: 72
 Joined: Mon Oct 27, 2014 10:50 am
 OLAP Product: TM1
 Version: 10.2.2
 Excel Version: 2013
Re: can anyone suggest approch to write TI/ Rule for implementing interpolation in TM1
If I solve it for you. Can I send an invoice?

 MVP
 Posts: 1944
 Joined: Mon Dec 29, 2008 6:26 pm
 OLAP Product: TM1
 Version: PAL 2.0.6
 Excel Version: 2016  Office 365
 Location: Brussels, Belgium
 Contact:
Re: can anyone suggest approch to write TI/ Rule for implementing interpolation in TM1
Sure you can.
Not so sure if it will be paid, though.
Yesterday I received an email for my services. Pretty hopeless in my opinion
Best regards,
Wim Gielis
Excel Most Valuable Professional, 20112014
http://www.wimgielis.com ==> 107 TM1 articles and a lot of custom code
Newest blog article: Turbo Integrator: report on progress
Wim Gielis
Excel Most Valuable Professional, 20112014
http://www.wimgielis.com ==> 107 TM1 articles and a lot of custom code
Newest blog article: Turbo Integrator: report on progress

 Posts: 5
 Joined: Mon Feb 04, 2019 12:38 am
 OLAP Product: TM1
 Version: 10.2.2
 Excel Version: 2016
Re: can anyone suggest approch to write TI/ Rule for implementing interpolation in TM1
Sorry , i am not looking for the entire solution right now for this prototype . i am commercial analyst trying to make use of our TM environment for our future business cases .
i am able to get the percentage change in Monthly RRP using DIMIX and DIMNM functions in rules . Curious to get suggestions/ Insights on how scan through each row and identify the first available mac rate then apply reduction backward and then go to another available MAC Rate apply and so on . then increment the same percent change as explained in part 4 in the excel .
Also The elements in the bandwidths are not necessarily place in an ascending / descending order . for example : lets say dimension has 1000M/400M
,ADSL ,2000M/2000M, 2250M/2250M in the oder with indexed 100, 101, 103,104 . the actual numeric equivalent of ADSL is Lesser than 1000M/1000M
The numric equivalent i am storing as attribute in Bandwidth dimension
Also this calculation should be valid for all discount scenarios explained in my previous reply.
Any leads welcome .
i am able to get the percentage change in Monthly RRP using DIMIX and DIMNM functions in rules . Curious to get suggestions/ Insights on how scan through each row and identify the first available mac rate then apply reduction backward and then go to another available MAC Rate apply and so on . then increment the same percent change as explained in part 4 in the excel .
Also The elements in the bandwidths are not necessarily place in an ascending / descending order . for example : lets say dimension has 1000M/400M
,ADSL ,2000M/2000M, 2250M/2250M in the oder with indexed 100, 101, 103,104 . the actual numeric equivalent of ADSL is Lesser than 1000M/1000M
The numric equivalent i am storing as attribute in Bandwidth dimension
Also this calculation should be valid for all discount scenarios explained in my previous reply.
Any leads welcome .
Last edited by valletchuman on Wed Feb 06, 2019 12:46 am, edited 3 times in total.

 Posts: 5
 Joined: Mon Feb 04, 2019 12:38 am
 OLAP Product: TM1
 Version: 10.2.2
 Excel Version: 2016

 Posts: 5
 Joined: Mon Feb 04, 2019 12:38 am
 OLAP Product: TM1
 Version: 10.2.2
 Excel Version: 2016
Re: can anyone suggest approach to write TI/ Rule for implementing interpolation in TM1
i got the solution and Implemented whole solution prototype using rules .Thanks for sharing your thought . May be we will plan to scale up this prototype . Now i can confidently say any complex calculation is possible in TM1 .

 Posts: 4
 Joined: Wed Sep 11, 2013 6:52 am
 OLAP Product: tm1
 Version: 10.2
 Excel Version: 14.0
Re: can anyone suggest approach to write TI/ Rule for implementing interpolation in TM1
These type of statistical calculations can be done with the help R I think.
Using the package tm1r [https://cran.rproject.org/web/packages/tm1r/index.html]
Using the package tm1r [https://cran.rproject.org/web/packages/tm1r/index.html]