History Modifier With Examples

A history modifier makes a formula calculate data from a past time period. With history modifier, you can build formulas that calculate data from historical periods such as

• The previous five days,
• Two weeks before the beginning of the current month,
• The past two years,
  and more.

The history modifier can be found in the data function component in the formula editor. The modifier is directly related to the time period you select on a widget. However, it does not set or substitute time periods – a history modifier simply tells the formula to look at historical data, on a rolling basis.

The available time periods are Day(s), Week(s), Month(s), Year(s). The historical data the formula calculates from your data source is relative to the time period selected on the widget.

Example

By adding a history modifier to a data function component, you can count the number of sales deals from the previous 5 days. The formula would look like the following:

Let's break down how a dashboard widget would display data from the formula above if different time periods are selected.

• Today: The widget will display data from five days back, starting from yesterday. For example, if today is June 10, it will show data from the 5th until the 9th of June (both days included).
• Current week: The widget will display data from five days back, starting from the last day of the previous week. For example, if today is June 10 and the week starts on June 7, the widget will show data from the 2nd until the 6th of June (both days included).
• Current month: The widget will display data from five days back, starting from the last day of the previous month. For example, if today is June 10, it will show data from the 27th until the 31st of May (both days included).
• Current year: The widget will display data from five days back, starting from the last day of the previous year. For example, if the current year is 2022, the widget will show data from the 27th until the 31st of December 2021 (both days included).

See the performance today compared to yesterday on the same dashboard widget. The easiest way to create this KPI is by copying an existing formula, adding a history modifier to the duplicate, and adding both formulas to a widget. Here are the steps:

1. Copy an existing formula.

2. Rename and add a history modifier to the copied formula.

3. Add both formulas to a widget such as the number box. Set the time period to Today.

See your performance this month compared to the same month last year. The easiest way to create this KPI is by copying an existing formula, adjusting the duplicate, and adding both formulas to a widget on your dashboard. Make sure to set the time period on the widget to Current month. Here are the steps:

1. Copy an existing formula.

2. Rename the copied formula.
3. Duplicate the component – click on the three little dots in its top-right corner.
4. Add history modifiers to the components as shown in the image below.
5. Change the operator to minus ➖.

The modifier in the first component takes data from the previous year (you can also use 12 months) and the second takes data from the previous 11 months. All we need is to subtract the 11 months of data from the 12 months to get the metrics for the same month last year.

6. Add both formulas to a widget. Set the time period to Current month.

Assume you have a manual data source containing all your employee targets, but there is only one registration per employee that gets updated if the target changes. Knowing that each registration has a date field linked to it, if a registration does not get updated for a while, it might not fit within the time period selected on a widget and therefore not display the target.

To go around this limitation, you can add a history modifier to the formula to display the employee target even if it is outside the selected time period on the widget. To do so, you should create a formula using the Last data function.

In this formula, you can freely decide how much of the historical data you want to draw. If you know that the employee targets get updated rarely, you can add more time to the modifier, for example, 3 years. See the formula below:

The available history modifiers in the Advanced formula editor are Day, Week, Month, Year. Please note that the history modifiers are written as singular nouns: "day" instead of "days," "month" instead of "months," and so on. Here are some examples:

.History(2,day)
.History(3,week)
.History(10,month)
.History(3,year)

The video below shows how to create formulas in the advanced editor and see the number of leads on the current day compared to the previous day.