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
Best Answer
-
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!
0
Answers
-
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.
0 -
Thank you for replying
I used Timesum(Average) in a module that has no dimensions but it gave me a huge value.
PFA screenshots @JaredDolich0 -
Has there been any fix created for this yet?
0 -
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 ?0 -
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 ?
0
