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

Prevent end users deleting a list item when using Worksheet in UX.
Hi, I have a worksheet for end users to enter new hires into, for which the list item which persist across versions. I am having an issue where the end user can delete a list item using the grid functionality because the Current Version value is blank, but I want to prevent them deleting them (as data will be lost from…
Introducing Anaplan API Python SDK
Hey everyone, I wanted to share the Anaplan Python SDK with you. It's a Python Library that wraps all Anaplan APIs and makes it easier to interact with Anaplan programmatically. It is mostly designed with Data Integration Scenarios in mind, but it does support all APIs including ALM, SCIM, Audit, and Cloud Works. Please…
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…