MOVINGSUM Formula

Bhavesh

Hi,

I have a snapshot of what I require done in excel but can't get the MOVINGSUM formula to replicate this.

I have Pass Sales on a Generic Weeks Timescale model. The pass is then spread out by the number of days in each week that it applies to.

Basically I'm trying to defer revenue over the time of the pass.

Sales in the 1st week need to take a portion (3/20) then 2nd week (7/20), etc as shown in the snapshot. Then the following week pass sales of 2,826 is supposed to take a portion from (7/17) and so forth then added to the 1st week sales.

Can someone please guide me and let me know if this is possible to do in Anaplan.

Thanks.

rob_marshall

@Bhavesh

Yes, it can be done and you don't need the movingsum().

This is my "data" module, dimensionalized by Time at the DAY level. Notice, I do have this filtered so the data can be seen in one picture.

Blueprint

I created a new list (Fake Time) for your Correctly Working line items. A better way to do this is to mimic the "true" days in this list instead of me removing the year.

Created a SYS Module using the Fake Time to "anchor" the members to the correct day of the calendar. If done correctly (with the Fake Time members mimicking the true time, this would just be a finditem() which will make it much easier).

Created a RPT module

Where the formulas are:

Final: Stage Pass Sales / Stage Pass Remaining Days * Data.Pass Days by Week
Anaplan Calcs: No Data
Valid?: START() = SYS Fake Time.Map to Time
Stage Pass Sales: IF Valid? THEN Data.Pass Sales ELSE PREVIOUS(Stage Pass Sales)
Stage Pass Remaining Days: IF Valid? THEN Data.Pass Remaining Days ELSE PREVIOUS(Stage Pass Remaining Days)

For a Final Result (again, please note I have a filter on the Time to show you the correct columns):

Hope this helps,

Rob

Bhavesh

Hi Rob,

Thanks very much for the well detailed answer. Appreciate it.

VanshikaSingh

@rob_marshall ,
Can we use time setting as weeks to have 18 aug, 25 aug so on…… and apply time as dimension in module , instead of making hard coded list?

rob_marshall

@VanshikaSingh

To be honest, I do not understand your question as the above does use the Time dimension (at the day level rolling up to the Weeks) and nothing is hardcoded, save the mapping module.