Calculation Inquiry

Highlighted
Community Boss

Calculation Inquiry

Hi,

 

PLease see details in the attachment.

 

Regards,

13 REPLIES 13
Highlighted
Master Anaplanner/Community Boss

Re: Calculation Inquiry

@Jsdeloria21 

 

You will have to create a SYS TIME module and have a mapping between Native Time and Counter Time. For Example Jun 2020 will be Month 10 something like that. Once done you can easily use LOOKUP based on the mapping and pull the values. Let me know if you want me to create an example for you

Highlighted
Community Boss

Re: Calculation Inquiry

Hi Misbah,

 

Thank you for the response. I kinda figured that this is going to use the LOOKUP function. Is it ok if you can show me how to do this approach?

 

Regards, 

Highlighted
Super Contributor

Re: Calculation Inquiry

Hi @Jsdeloria21 - 

Follow these steps

 

41.JPG42.JPG43.JPG44.JPG45.JPG

 

Thanks,

Sorna Raja Prabhu
Highlighted
Master Anaplanner/Community Boss

Re: Calculation Inquiry

@Jsdeloria21 

 

@Sorna Raja Prabhu  has created an example for you already. The only thing I would add is if you have a logic you can automate the mapping between Native Time and Counter Time, if not then you can manually map these two

 

Hope that helps

Misbah

Highlighted
Certified Master Anaplanner

Re: Calculation Inquiry

Hi @Jsdeloria21 ,

 

I think we need to create a staging module with Payment Scheme, Time, and Counter Time as lists.  This will enable us to calculate the installment for each principal amount. The final module will summarize this staging module.

 

Principal.JPG

 

From your example, I notice that percentage split is always input from Month 1, no matter the real Month when the principal amount is issued

 

Percent.JPG

 

The first staging module is to perform the calculation for each principal

 

Principal Staging.JPG

Principal Staging-Formula.JPG

 

The second staging module is to summarize the payment for each Counter Time using TIMESUM

Principal Staging 2.JPG

The result module needs to convert the Counter Time to Anaplan Time using SUM.  Here is shown the Oct 19 - Dec 19 (which corresponds to Oct 18 - Dec 18 in your example)

Result.JPG

In addition, we need the mapping module that @Sorna Raja Prabhu showed, and for my example the reverse mapping module which are derived by formula from the original mapping

 

Reverse Mapping.JPG 

 

Regards,

Andre

 

Highlighted
Community Boss

Re: Calculation Inquiry

Hi Andre,

 

My apologies for the late reply on your response. And I want to say thank you for taking time on answering the query. I would like to ask something about your solution particularly on the table below. I can see that the Counter Time column on the list was already set to Month 1 - Jan 19. I already had the Column Counter time no ready. Just want to ask, did you set the Month 1 as Jan 19 on the list.? I mean did you assign it manually on this table below? What if there is a value on "Principal" which is Sep 16, does this mean Sep 16 should be tagged as Month 1? 

 

I am currently applying your solution to the set up that we have already. Just need some clarifications on this.

 

Jsdeloria21_1-1593409033211.png

Hope to hear from you

 

Regards,

 

 

Highlighted
Community Boss

Re: Calculation Inquiry

Hi Andre,

 

My apologies again I think I was wrong on analyzing the screenshots. I just want to clarify my first reply to you.

 

Is this a module where Row - Time and Counter Time = Counter Dimension ? If so, is it possible to view the formula of this module so I can compare it with what the client.

 

 

Jsdeloria21_1-1593410169835.png

 

Thanks

 

Highlighted
Certified Master Anaplanner

Re: Calculation Inquiry

Hi @Jsdeloria21 

 

No problem.

 

I have a Counter Time Mapping module to assign Counter Time to Anaplan period manually.  Month 1 should be mapped to the first period in the Time Settings.  In my case, Time Settings starts from Jan 19

 

Counter Time Mapping.JPG

Counter Time Mapping - blueprint.JPG

 

Then I have the reverse mapping in Time - Counter Time Mapping module, which is derived from Counter Time Mapping module.  

 

Time - Counter Time Mapping - blueprint.JPG

 

Here is Principal Staging formula, for 2 line items in question

 

Principal Staging - blueprint.JPG

 

Data from Principal Staging goes to Principal Staging 2

Principal Staging 2.JPG

 

Principal Staging 2 - blueprint.JPG

Finally from Principal Staging 2 to Payment module

Payment.JPG

 

Let me know if you have further questions.

 

Regards

Andre

Highlighted
Community Boss

Re: Calculation Inquiry

Hi Andrei,

 

Thank you for the response it really helped a lot.

 

Here is that I have so far. This is what my Principal Staging looks like now.

 

As you can see on the Percent Counter Time Lookup line Item I only have the "Month 10" while on your example it counts Month 1... Month 2.. etc.

 

Did I do something wrong or am I missing something?

Jsdeloria21_2-1593418401449.png

 

 

Jsdeloria21_1-1593418159723.png

 

Regards,