This guide will walk through the Time Setting in Anaplan models, as well as some best practice recommendations on automating the current time period to simplify formulas and dashboard views.
The Time Setting is a critical component of the model structure and should factor in long-term objectives and the purpose of a new model. Once established, it can be difficult to go back and change in a production model without causing significant impact, such as potential data loss.
The basics of the Time Setting allow you to set the default type of calendar (monthly vs. weekly), as well as set the start month for the fiscal year, the current year, and an option to set the current period. I’m going to skip the current period option for now and later demonstrate how we have automated this process in most of our Anaplan models, and explain why.
It’s important to map out your long-term objectives of the model when looking at the number of past and future years. If you decide to change this after going live with a production model, it can be more challenging to populate past data if needed for analysis. At the same time, you don’t want past/future years if they are not required for your business needs. Adding irrelevant years in the model can increase model space and processing time for any line items dimensionalized by month if you do not monitor the time ranges.
For example, in the model below, I only care about sales quotas in the current year. If I set my model up to include past year data that I’m not tracking, any module that is dimensionalized by month will take up space and processing time calculating 0’s in past months that aren’t necessary. To mitigate this problem, you should focus on setting your model up only for the time ranges that you need data for.
Alternatively, you can set a time range for a subset of months and then customize modules and/or line items accordingly to keep model efficiency. In this model example, I have both a current and one year of past data, but some modules I choose to dimensionalize by only the current year’s data. This is done by setting up a time range in the settings:
I mentioned above that I don’t set current period in a lot of my models. The reason for this is because we have chosen to automate the current period in order to streamline the monthly rollover process in our calculations. When it comes to time, the only thing we need is a single input feed: Current Date. We have this auto fed daily through our Anaplan Connect jobs, and from there we have created various time periods that we use in either our data comparison or time calculations. I’ll walk you through what we have set.
In each model we create, the first module we establish is a Date module. Here, we have our Current Date, which is fed from our Enterprise Data Warehouse (EDW) each morning. When importing the date, it’s important to do this outside of busy hours for your organization because this drives and updates calculations in the models. Our import is started before working hours so that it is complete when users log into the models in the morning. We then establish the current date, current month, current year, prior month, and prior year. The formulas are outlined below, and with the automation of our current date, we never need to update the day/month/year in the model to be accurate. Keep in mind our organization has standardized on one date regardless of location, this is an important fact if you are a global organization and can have people on different dates due to time zones.
The biggest benefit for our organization with this approach, besides the automation of dates, is that it simplifies many formulas throughout the model by using basic lookup functions. If users are building out a module and need to include the numbers from the current month, they can simply point to the module storing the data and do a quick [lookup: date.month] or [lookup: date.current year]. The summary function on the line item will auto sum the numbers when we are looking up a full year or any other time range we have defined.
The second module we establish is a time filter. This module is what makes our lives easier when it comes to dashboard building and comparing data from various time ranges. It is also one of the easier concepts for our business users to onboard and allows them to create their own unique time ranges based on their model needs.
Almost everything we use is set in the format of Boolean checkboxes. From here we use fairly simple (although they can get lengthy) formulas to establish time ranges by month—things like Current Year (CY), Prior Year (PY), Year to Date (YTD), Year to Go (YTG), Trailing 12 months (T12), Trailing 3 Months (T3M). As you can imagine, we have a lot of flexibility in the types of ranges we create, and these are the foundations that drive a majority of our calculations and dashboard views.
For calculations, we then build out modules dimensionalized by month, and we can reference the time filter module to form the calculations below.
Since the Date is automatically updated daily, this then updates all calculations and time filters as each month rolls over without any interaction from users. In June, the trailing three months are March, April, May. When the date switches to July 1, it knows that the trailing three months are now April, May, June, and all comparison data is automatically updated. We analyze things like how much quota is left in the year vs. how much has been given out which is determined by the YTD and YTG line items in the screenshot above.
These time filters are also the primary driver of our dashboards. When we want to filter our data to show business users data from the current month, we set the filter to be based on the Current Month line item of our Time Filter Module. When the month rolls over, the dashboard updates with it.
Another use case is when we export monthly data, we only want to export the current month and future months. Again, this is determined by a time filter so that past data never gets changed. The example below was taken from our model in October:
Our organization has also found this to be an effective alternative to Forecast vs. Actuals in many cases. We define our Actuals Periods with “ITEM(Time)>= Date.Current Month”.
The moral of the story is, don’t overlook the small stuff in Anaplan. The Time Setting aspect may seem like a quick and basic part of establishing a new model, but with one automated daily feed, it is arguably the most important aspect of all models we create and the easiest for our model builders to customize.