Moving Average in Custom Timescale

aishabhy05
Contributor

Moving Average in Custom Timescale

Good Day!!

 

I have a scenario where I'm trying to calculate the moving average from actuals in Custom Day time scale. In a module, I have sales data to be uploaded, from the sales data I have to calculate the moving avg of 90 days at the day level. Dimension in the module Article, Custom Day scale. Line Item -Sales  Qty, Sales Value.

 

aishabhy05_0-1593569680418.png

I tried using the formula -

Line item 1- CUMULATE(Sales Qty, FALSE, Custom Day)) / 90

Line Item 2- 'Line Item 1' - 'Line Item 1'[LOOKUP: Custom Day.'Current Day-90']

 

The Above formula gives average but not moving average. Any suggestions, please?

 

 

Thanks.

 

1 ACCEPTED SOLUTION

Accepted Solutions
rob_marshall
Moderator

Re: Moving Average in Custom Timescale

@aishabhy05 ,

 

This is how I would handle it.  Create a mapping module from Fake Time to Real Time and use the MovingSum() function on Real Time.  Then do a lookup to Real Time from the mapping module to get your data for reporting.

 

Rob

View solution in original post

9 REPLIES 9
jasonblinn
Master Anaplanner/Community Boss

Re: Moving Average in Custom Timescale

@aishabhy05 

 

Sorry i forgot to respond to your follow up on the other post. 

 

Here is how I would probably do this. 

 

You are on the right track with the Cumulate. But we just want to keep a running cumulate going:

 

jasonblinn_0-1593571222895.png

 

From there, we can then calculate what our 10 week ago week would be with a formula like the following: FINDITEM(Fake time, TEXT(VALUE(NAME(ITEM(Fake time))) - 10)) It would be best to stage this out to not calculate it all on one line.

 

jasonblinn_1-1593571305133.png

 

From there, we can see what the current value of the cumulative line is, subtract what it was 10 periods ago, and divide this by 10. : (Cumulate Line - Cumulate Line[LOOKUP: '10 week Ago']) / 10

jasonblinn_2-1593571370220.png

 

I am sure there are other ways to go about this as well, but this should work.

 

Let me know if there are other questions!

Jason

 

 

aishabhy05
Contributor

Re: Moving Average in Custom Timescale

Thanks @jasonblinn  for your swift response. The average is running, but I have an issue from next week/day the current week Average should be Considered. The above formula works well for moving average. But for forecasting the sales data Clients requesting the last 90 days average and for forecasting days it should include the Current Day Average. Any workaround for this, please?

 

aishabhy05_0-1593573547853.png

 

rob_marshall
Moderator

Re: Moving Average in Custom Timescale

@aishabhy05 ,

 

This is how I would handle it.  Create a mapping module from Fake Time to Real Time and use the MovingSum() function on Real Time.  Then do a lookup to Real Time from the mapping module to get your data for reporting.

 

Rob

View solution in original post

aishabhy05
Contributor

Re: Moving Average in Custom Timescale

Thanks @rob_marshall .  This is in line with my thought as well. 

 

I tried another method, not sure the following method is efficient

 

Steps: Input module Past Data in Custom Time

 

aishabhy05_1-1593614006744.pngaishabhy05_2-1593614022107.png

 

 

Step 2: Calculating Avg for Current Period

aishabhy05_3-1593614062131.png

aishabhy05_4-1593614098690.png

 

Step 3: Rolling the calculated forecast to future periods.  Using Subset every week it moves when the data will be uploaded. Calculated the Avg Sales Twice, not sure whether this method is efficient. 

aishabhy05_5-1593614257939.png

aishabhy05_6-1593614277031.png

 

I suspect calculating Avg twice may affect the performance. 

Welcoming Suggestions. 

 

Thanks 

 

 

 

rob_marshall
Moderator

Re: Moving Average in Custom Timescale

@aishabhy05 ,

 

Honestly, I think using a real time module and using MovingSum() will be easier and more performant than the hoops you are having to jump through.

 

Rob

LILYLIU
Frequent Contributor

Re: Moving Average in Custom Timescale

Hi Rob @rob_marshall ,

 

I just posted a new question inspired by this post.  I am working with custom timescale (days) but I needed to use the system days to do some calculation. I struggled to map the result from system time module back to custom time module. 

 

I was wondering if you could kindly elaborate the " Then do a lookup to Real Time from the mapping module to get your data for reporting." ? 

 

Many thanks,

 

Lily

rob_marshall
Moderator

Re: Moving Average in Custom Timescale

@LILYLIU 

 

I guess it depends on the format of your "custom" days.

LILYLIU
Frequent Contributor

Re: Moving Average in Custom Timescale

Thank you so much Rob. @rob_marshall 

 

Below is a copy of my question. 

 

I have below module using Days as time dimension. I also have a line called fake days where I mapped the system days to a fake days list.  

LILYLIU_0-1622734827765.png

 

Then I would like to do a LOOKUP to pull the data from the above module to a module with the fake day list as dimension.  Time is not a dimension.

 

LILYLIU_1-1622734827729.png

 

 

Thanks for your help.

 

Lily

https://community.anaplan.com/t5/Anaplan-Platform/Map-Result-from-System-Time-Days-to-Customer-Days-...

 

 

rob_marshall
Moderator

Re: Moving Average in Custom Timescale

I will reply in the other thread