TM1 Forum

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

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

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 ,

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

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

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 .

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 .

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 .

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]