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

Post Reply
valletchuman
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

Post by valletchuman » Mon Feb 04, 2019 3:41 am

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
Attachments
example_Prot.docx
(72.66 KiB) Downloaded 12 times
Last edited by valletchuman on Wed Feb 06, 2019 12:03 am, edited 1 time in total.

User avatar
paulsimon
MVP
Posts: 664
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

Post by paulsimon » Mon Feb 04, 2019 11:38 pm

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

valletchuman
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

Post by valletchuman » Tue Feb 05, 2019 12:58 am

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_calc_excel model.xlsx
sample
(18.07 KiB) Downloaded 12 times
sample excel model i think of attached in this email.

Looking forward for your insights / thoughts to implement this in TM 1 .

Thanks ,

Bakkone
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

Post by Bakkone » Tue Feb 05, 2019 1:15 pm

If I solve it for you. Can I send an invoice?

Wim Gielis
MVP
Posts: 1939
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

Post by Wim Gielis » Tue Feb 05, 2019 2:42 pm

Bakkone wrote:
Tue Feb 05, 2019 1:15 pm
Can I send an invoice?
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, 2011-2014
http://www.wimgielis.com ==> 107 TM1 articles and a lot of custom code
Newest blog article: Turbo Integrator: report on progress

valletchuman
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

Post by valletchuman » Tue Feb 05, 2019 11:46 pm

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 .
Last edited by valletchuman on Wed Feb 06, 2019 12:46 am, edited 3 times in total.

valletchuman
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

Post by valletchuman » Tue Feb 05, 2019 11:48 pm

Bakkone wrote:
Tue Feb 05, 2019 1:15 pm
If I solve it for you. Can I send an invoice?
FYI , i am not looking for solution . i am only looking for the suggestion / Approach .

valletchuman
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

Post by valletchuman » Fri Feb 08, 2019 2:34 am

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 .

muhammed.onder
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

Post by muhammed.onder » Fri Feb 08, 2019 8:02 pm

These type of statistical calculations can be done with the help R I think.
Using the package tm1r [https://cran.r-project.org/web/packages/tm1r/index.html]

Post Reply