How to get the average of a previous month in the current month ?

Hi

I have a grid where in I need to calculate the average of the commissions selected for a couple of months and then to send it to another grid of a different month. How do I do it ?
PFA screenshots
Need help urgently

Thank you

Image_1_145018.png

Image_2_145018.png

Accepted answers

JaredDolich

@tinathom

Yep use TIMESUM, not CUMULATE and only sum on the months you want, presumably the last three months. Use a dimensionless module to calculate the last three months so you aren't hard-coding. You've almost got this!

All comments

JaredDolich

@tinathom

MOVINGSUM would be your best choice since the TARGET module also uses time. In a clutch, you can also use TIMESUM but that is not a very efficient function. If you use MOVINGSUM you'll need to also add up the denominator so you can calculate the average. This can be done by using a counter (If commission >0 then 1 else 0). This will give you the count to use to create the average.

So, MOVINGSUM(Commission of the months you're interested in) / Counter = Average commission.

Now, if you only need one value for the current month, then I would suggest you create a reporting module that is not dimensioned with time at all. This will allow you to use TIMESUM.

tinathom

Thank you for replying
I used Timesum(Average) in a module that has no dimensions but it gave me a huge value.
PFA screenshots @JaredDolich

Formula_used_145053.png

Guarantee_1_145053.png

JaredDolich

@tinathom

Curt866

Has there been any fix created for this yet?

MYiClubOnline

tinathom

Hi @JaredDolich
Thank you for the help and the encouragement . This helped me a lot. Appreciate it.

PFA the screenshots.
This looks good now right ?

Image_of_Module_2_formula_145094.png

Image_of_Module_1_145094.png

Image_of_Module_2_145094.png

Image_of_Module_1_Formulas_used_145094.png

tinathom

Yes @Curt866 If you want , we can discuss.

jehular

Hi, Would like to ask If I want to get the average of Jan 22 and Jan 23. Is MOVINGSUM function would be the best choice to use ?

Quick Links

Can you help?

Unanswered questions

ERROR 16020 |-- Anaplan API: Invalid key path
Hello everyone, We are trying to use Anaplan Connect in order to push data from a dwh to Anaplan Data Hub. To do so, we need an authentication by token. We can't make it work, we receive the following error: ERROR 16020 |-- Anaplan API: Invalid key path Any suggestions? Thank you all in advance for the help! Michele
AnaplanXL Difficulties
Has anyone found success using Anaplan XL with Polaris models in a shared drive with or without concurrent users? My team has experienced a variety of errors, login failures, etc. sometimes it works, sometimes it doesn't. We've been recommended to save and edit files locally, which helps but certainly with its limitations…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…