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

Post Reply
valletchuman
Posts: 11
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 »

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 166 times
Last edited by valletchuman on Wed Feb 06, 2019 12:03 am, edited 1 time in total.
User avatar
paulsimon
MVP
Posts: 808
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 »

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: 11
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 »

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 170 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: 119
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 »

If I solve it for you. Can I send an invoice?
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: can anyone suggest approch to write TI/ Rule for implementing interpolation in TM1

Post by Wim Gielis »

Bakkone wrote: Tue Feb 05, 2019 1:15 pmCan 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

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
valletchuman
Posts: 11
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 »

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: 11
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 »

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: 11
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 »

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 »

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