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,

Accepted answers

Misbah

@Jsdeloria21

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

@Jsdeloria21

@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

All comments

Misbah

@Jsdeloria21

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

Jsdeloria21

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,

Misbah

Sure. Let us know if it works

Jsdeloria21

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,

Misbah

@Jsdeloria21

@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?

Hope that helps

Jsdeloria21

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,

Misbah

@Jsdeloria21

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

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…