Skip to main content

 

Chances are you’ve set up your calendar in your application and have grown curious about period types.

Period types can be very useful either as a filter (adding the Dimension along with Months helps isolate the actual from the forecasted data) or within formulas.

The ‘Switchover Date’ is a predefined Pigment Metric that is used to determine the point (or date) in which months are no longer actuals, but are forecast instead. The ‘Switchover Date’ will define your period type as “Actual” or “Forecast” based on this date. It represents the most recent date at which the data is updated. By default, this Metric has no formula, but rather is a manual input. Mostly, it’s easier this way, and you can update it every time you upload your data.

 

 

Automating makes data more reliable

 

A two step manual process is not as reliable as a calculated process - so you should consider calculating the ‘Switchover Date’. Most of the time, you’ll be basing the ‘Switchover Date’ over a particular set of data, which would be a Transaction List in Pigment. For this example, we’ll use a Sales list.

 

An example transaction list

 

Here, you can note that the data is not in a particular order.

The ‘Switchover Date’ can be calculated by taking the maximum of the Date property (or the date of the last transaction).

If your data has a date, you can use the MAX aggregator, which conveniently works with dates.

 

Sales.Date-REMOVE MAX: Sales]

 

 

However, if your raw data has no date but rather a Property based on the native calendar (e.g. Month), you may still use it by using the Properties of the Calendar Dimension. Here, we’ll use the Start Date Property of the Month Dimension.

Sales.Month.'Start Date'2REMOVE MAX: Sales]

 

However, in this example, the first day of the month is used rather than the actual transaction date.

 

 

Changing the behavior of the period type

 

If you don’t like how the period type is selected from the ‘Switchover Date’, feel free to add another one! Simply create a new property of format Dimension (Period Type), copy the original formula there and adjust it to your needs!

 

 

By default, this is calculated in the formula of the Period type Property of the Month Dimension.You can adapt this to whatever is needed in your Application, of even add a second Period type Property based on another data set.

 

 

Can you share why REMOVE is the aggregator you use here? Seems strange that you aren’t using BY or something else.

 

That said, this is a fantastic use of the data with the switchover functionality. Amazing other vendors don’t do this.

Hi Bill,

The BY modifier goes from a dimension to another, and in that case I’m going from one dimension / transaction (Sales) to none.

 

Here’s a small recap from our training material:

 

Hi @francois ,

Thanks for the article, I tried to update the Period Type property, but I received the following error message:
“Formula compilation failed: Unable to update the formula on the property: Period type as it is protected”

I have Admin rights though; do you know what could cause this issue?

 

Hi!

Well spotted! It seems the product has evolved since the writing of the article, and the Period Type property has been protected.

You should still be able to safely add any other property, with the same data type in your calendar though, but named differently. I’ve changed the original topic to reflect this.

Hope this helps.

Reply


Ask your question here!

Terms & ConditionsCookie settings