Weighted average, similar to sumproduct in excel

Hi all

I have 4 line items vis. Actual demand, D1, D2,D3, Forecast demand. Having monthly time dimension for FY 20.

Need to calculate Forecast demand for each month based on this calculation

Forecast for April 20 = (Actual demand for Jan 20 * D1+Actual demand for Feb 20 * D2 + Actual demand for Mar 20 * D3)/ sum D1 +D2+D3

Basically its a 3 month moving average, considering weights at the same time.

File is attached for your reference

Thanks

Accepted answers

Hi @anupgade - See this image, for better understanding.

Thanks,

Sorna Raja Prabhu

Have you thought about using MovingSum()?

Rob

Yes, but the question arises how to incorporate those weights in the calculation.

Hi Anup,

Can you please illustrate further, what would be the forecast for May 20 and Jun 20

Regards,
Anirudh

You can accomplish in 2 ways

Major task is to get the 3rd, 2nd, 1st month data (Actual).

Approach 1:

1. Create a system Module, where you should map current month with past 3rd month (using formula)

refer this line item for calculating weighted average.

apply the similar logic for getting past 2nd and 1st month data(Actual).

Approach 2:

1. Try movingsum() function,

i will give you an hint, check if it helps.

movingsum(actual, -3,-3,sum) - to get 3rd month data

apply similar for getting 2nd and 1st month

Thanks,

Sorna Raja Prabhu

Hi @anupgade - See this image, for better understanding.

Thanks,

Sorna Raja Prabhu

Thank you
@prabhu

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…