A guide on how to create a target formula where the target is increasing every hour.
Create an IF statement to display a target value that increases every hour, based on the relevant time of the day. The IF statement will return a target value that updates every hour, provided that the data in your data source also gets updated. For better visualization, enable custom conditional colors on the widget or add conditional colors in your advanced formula editor.
Imagine you have created a speedometer widget that uses conditional colors and displays your team's current performance compared to a daily target. Since the team might reach the target only towards the end of the workday, the speedometer will show red for most of the day (see the image below).
An example of the different options for visualizing actual values vs. daily targets. The clock widget illustrates at what moment the widgets are still displaying the progress in red.
It can be demotivating for the team to see their KPIs in red and think they are falling behind the target, especially if it is not the case. Therefore, you can create a formula that increases the target value each hour and thus helps your team have a better understanding of their progress.
An example of the different options for visualizing actual values vs. hourly targets. The clock widget displays the current hour to illustrate that if the working day starts at 9 am and the hourly target is 5, at 2 pm, the target value will be 30.
It is common to add target formulas to widgets, such as the number box or speedometer because they allow for better progress visualization with the help of conditional colors. However, feel free to apply the KPIs to your dashboard as you please.
To create an IF statement that uses a static number as the hourly target, do the following:
- Go to KPI Management > Formulas.
- Click + New formula > Switch to Advanced editor in the top-right corner.
- Write down a formula similar to the following. This formula expects an hourly target of 5 (it could be five leads, calls, completed tasks, or other KPIs). The working hours are between 9 am and 5 pm (9-17), and the total workday amounts to 8 hours.
( 5* ( IF(Hour(Now())>9, IF(Hour(Now())<=17, Hour(Now())-8, 0), 0) ) )
Note: If you use this formula example, make sure to adjust the hourly target as well as the working hours in the formula based on the targets and working hours of your organization.