about sum functon ho we can get the data last from 6 months actuals?

kavitha12345
Occasional Contributor

about sum functon ho we can get the data last from 6 months actuals?

i have actuals data from last 6 months ..i have created last 6 months line item to get the data here ..

Then  how can i write the formula here ..

Example

                          Jan  Feb   Mar   Apr  May  Jun  July    Aug  Sep

Actuals             100   200     300 400  500    600   700  800    900

last 6 months     0        0       0    0      0          0     0      0      0

Present my module is like above format

so i need last 6 months Avg data in sep Month .

 

Thanks in Advance

 

7 REPLIES 7
rob_marshall
Moderator

Re: about sum functon ho we can get the data last from 6 months actuals?

@kavitha12345 

 

MovingSum() shoud help here as long as you are using Native Time.

 

Rob

kavitha12345
Occasional Contributor

Re: about sum functon ho we can get the data last from 6 months actuals?

i am using custom time scale 

MarkWarren
Expert

Re: about sum functon ho we can get the data last from 6 months actuals?

You should consider using a real timescale, will help a lot if you need to do such time based calculations. However,  you could create a mapping module, to map your custom time to real time, lookup the data on a real timescale so you can use Time based formulas.
Or a simple and performant way to get this value would be to create a 6 month subset in your custom time list and reference the Actuals with an Average summary option set for the line item (assuming you have a top level).

Sampriti.Anand
Contributor

Re: about sum functon ho we can get the data last from 6 months actuals?

Hi @kavitha12345 ,

 

I have created a module Test Time where the actuals data of the past three years is stored. I have created another module Test Time - Copy where I have created a line item to derive the last 6 month from the current month and then using lookup the values from Test Time module have been fetched into Test Time Copy. I have used Custom Time Ranges in both the modules. A similar approach will be of help to you.

SampritiAnand_0-1632149502751.pngSampritiAnand_1-1632149530566.png  

SampritiAnand_2-1632149558097.png

Thanks!

 

 

 

kavitha12345
Occasional Contributor

Re: about sum functon ho we can get the data last from 6 months actuals?

Thanks
ChrisHeathcote
Community Boss

Re: about sum functon ho we can get the data last from 6 months actuals?

An alternative is to map your custom time to real time. Then map your data out of custom time into a module dimensioned by real time. Use this to calculate your MOVINGSUM() then map it back to custom time use the same mapping. 

The actual real timescale is not important but it is vital that you map into real time and back out to custom time using the same logic. 

The use of real time is simply to use all available time functions. 

However, if you can avoid using custom time altogether then I would also recommend that you do so. 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
Amaya
Certified Master Anaplanner

Re: about sum functon ho we can get the data last from 6 months actuals?

Hi @kavitha12345 ,

 

You have to use the Model Calendar, as everyone says.

 

I think you can do this, but it will be complex and ugly.

CAUTION! So do not take the below seriously.

 

ModuleModuleGridGrid

 

Taichi