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
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.
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”
Create a new list, I called mine Months (Properties: Start Date, End Date)
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
Base Month, Format: List.Months
Comparative Month, Format: List.Months
These are to lookup the selected months properties
Base Date, Format: Date, Formula: Months.Start Date[Lookup: Base Month]
Comparative, Format: Date, Formula: Months.Start Date[Lookup: Comparative Month]
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
**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.
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.