Reporting Month over Month

All: Anyone have any suggestions or options for building a reporting that is MONTH versus MONTH, inside a specific version or across versions? Our customer requires an ability to compare JUN FCST 2014 against MAY FCST 2014. or JUN FCST 2014 against JUN FCST 2013? We have ideas, but are looking for external ideas from the larger community. Thanks in advance. Petar

Accepted answers

PrevContributor

Hi Petar,

You could create a module that does not have Versions as a dimensions and use the folowing line items for the case of Jun 2014 FCST v May 2014 FCST:

Line Item 1: Module.Line Item[SELECT: Versions.Forecast]

Line Item 2: Post('Line Item 1', -1)

Line Item 3: Line Item 1 - Line Item 2

For Line item 2, you can substitue the -1 for any number of periods you wish to go back, e.g. -12. The syntax is POST(Value, Number of Periods). A negative number will return a period in the past whereas a positive will pull a period in the future.

Thanks,
Ryan

simon_ritchie

Hi Peter,

If you need to allow the users complete flexibility to select any 2 months to compare then the below might be of interest.

We had this requirement recently working with a CPG company who needed to analyse the changes between months/versions and perform a Volume, Price & Mix Variance calculation.
Below is one of a few different ways you could model it in Anaplan.

This is what my sample source data module looks like. It’s called “PL Summary”
**See screenshot**

Create a new list, I called mine Months (Properties: Start Date, End Date)

**See screenshot**

Create a new module, this will be used by the user to select the two months to compare
Add 4 Line Items:

These are for user selections
[list=1]
Base Month, Format: List.Months
Comparative Month, Format: List.Months

These are to lookup the selected months properties
[list=1]
Base Date, Format: Date, Formula: Months.Start Date[Lookup: Base Month]
Comparative, Format: Date, Formula: Months.Start Date[Lookup: Comparative Month]
**See screenshot**

Using the selections we can pull the data from the source module into a Comparison module.
Create the comparison module with the same dimensions as the Source, don’t include Time or Version
Add the following line items
[list=1]
Base
Comparison
Variance
Variance %
**See blueprint screenshots**
Formula for the base Line Item is: PL Summary.Value[SELECT: VERSIONS.Actual, LOOKUP: Comparison Selection.Base Date]
Note: In Anaplan time is built in, so by saying, Lookup a date, it will grab the number for the month that the date is in.

I added some variance formulas and some conditional formatting on the Variance %, then created a dashboard.
The user can now compare any month, against any other month.
**See screenshot**

Notice that I published the Month Selection line items to the dashboard and placed them at the top of the dashboard to make the user experience nicer.

Note: This approach only allows 1 choice of months to compare, it works well in a single/low user scenario. Where you have many users that want to analyse the results and select different months to compare they will trip over each other. The was around this is to introduce another list into the Comparison Modules and use selective access so that each user has their own item in this list. Hope that I have explained it clearly.

For simplicity, the above assumes you are only comparing Actual data only. If you need to allow the user to compare different version/month combinations then you would expand on the above approach to allowusers to select a Base and Comparative version also and tweak some of the formulas.

Get in touch if you have questions.

Hope it helps.
Kind regards,
Simon

All comments

PrevContributor

simon_ritchie

Create a new list, I called mine Months (Properties: Start Date, End Date)

Quick Links

Can you help?

Unanswered questions

ANAPLAN XL View
Hi, I need help in pulling a specific view in Anaplan XL. The first grid should display attributes of various projects, while the second grid should present the monthly financial data for these projects. Thanks in advance.
Add list members to a selective access list
Hi, I am working on a solution where the User should only have access to write/edit the list members that they add from an Action on the frontend. This action adds another list member to a selective access list. The problem is, in order to run the action, you need access to the entire list, so you would see all list…
Dimensionality Issue
Hi ALL, In my model, I have Need stored at a 'D3' RS Group level. I need to use this Need in another module at the G5 Location level. I currently have a mapping module where each G5 Location maps to a D3 RS Group. This Module uses D2 Role as dimension. but the line item I've to find the RS Group (Find D3 RS Group = 'FIND…