SUMProduct in Excel
Hi Guys,
I would like to ask help on this issue that I have never encountered before. In the clients excel sheet they are using a SUMPRODUCT formula. The Logic is multiplying the Start of the month (which is the starting month for their calculation) to the End of the month of the calculation engine but backwards.
Let me demonstrate the action:
Below is the 1st line item (which is a list) with values from Jan 16-Dec 16.
2nd Line item (which consists of the same list as above) has percentage but the value starts at the Month of Dec 16 backwards. Also, there is a line above labeled Month Spread. This is sort of their tagging per month on what month is the 1st month to start their calculation backwards.
Base on their formula, the multiply the Value 1.Jan 16(which is 2) to the Payment Scheme 1.Dec 16 and so on. See below
as you can see in the table above, Value 1(Jan 16) * Payment Scheme 1 (Dec 16) for the month of Jan 16, Value 1(Feb 16)*Payment Scheme 1(Nov 16)+ the value in JAN 16 and so on.
Hope you can understand the illustration that I showed you since I cannot really post the actual report for you.
Appreciate the help here.
Regards,
Best Answers
-
What happens when it crosses Jun month of the year? Anyway that is the assumption that I have taken
Step 1: Create Fake Months List
Step 2: Create a lookup module - which will tell Anaplan from where the numbers are to be pulled
Step 3: Create a SYS Time Module which maps Fake Months to Native Time. Here I have created Subs views, but you refrain from doing so - (See Step 4 Screenshot)
Step 4: In your Result Module write the formula for Result 1 Line item as follows. Breakup these formulae and don't clutter the way I have cluttered it.
Result:
Source Module 1
Source Module 2:
Target Module:
Hope this helps
Misbah
0 -
@1. On the below list (which i have done already), how did you determine which month is assigned to the mapping line item?
Misbah: This is the LOOKUP module, I just map it the way you had shown in an example. If there is any logic to it you can apply that as well in the same module instead of keeping it open for inputs. Bottom line is I manually mapped it without any logic.
2.. On the table below, i notice they have subsidiary view. What time range did you use on this?
Misbah: Don't Use Subsidiary Views. Create a SYS time module with Time as a dimension and keep all these line items there in the SYS module. In this example I had two dimensions one version and second time. I didn't want version to applied on all these line items hence seen as subsidiary views but you just create first two line items in the current module and keep rest all in SYS time module.
Hope that helps
0
Answers
-
Hi Misbah,
Appreciate the response. To answer the question what happens when it crosses the month of June. It will still multiply it to whatever the values is present on the line item. The line item 1 is also calculated in their report sheet. I will try to check your approach in the model.
Thanks again,
Regards,
0 -
Sure. Let us know if it works
0 -
Hi Misbah,
I am trying to use the approach you did regarding this concern. I just have a few questions if you don't mind.
1. On the below list (which i have done already), how did you determine which month is assigned to the mapping line item?
2. On the table below, i notice they have subsidiary view. What time range did you use on this?
Apologies for the late reply, I am trying to fit your approach to the current model we have.
hope to hear from you. Thanks in advance
Regards,
0 -
Hi Misbah,
Apologies for inquiring the same issue, I was able to somehow replicate the solution you provided and presented it to the client. However, upon our discussion some aspect suddenly changed. The engine as the client referred to it is actually not time bounded. The client made a mistake by putting the Calculation engine in the report section (where is it time bounded) and we made a mistake by assuming that it is time bounded.
This is the new illustration base on the new discussion:
This is the new look of the Engine, no time dimension present just a fix table. However, the counter line item will be inputted depending on the spread. In this case, I created 24 months spread. In their report it was 48 months but client also mentioned that it can reach 60 months.
This is suppose to be the result.
Can I still use that same approach that you presented before? Also, is there a way that we can create a list where the list members can be determine by the value inputted by the user (this is pertaining to the Counter line item)?
Appreciate the help on this
Regards,
0 -
Unfortunately no, same solution can't be implemented because when you take Time Scale away PREVIOUS Function won't work. I can think of using CUMULATE or LOOKUP depending upon the understanding of the use case, but I am not certain yet.
Clarify! When you use a list from 1-60, Does 1 refer to last month of timescale i.e., Dec 2020. If so, the how are you going to keep it moving when the year changes?
If Dec 2020 has values where should those values be pulled to for calculations, it looks like you are pulling it in May month and then multiplying it
0