Calculate the current date in Pigment
Excel function: Today()
For some use cases it can be a requirement to calculate something based on the current date. With the recently released Pigment Metric to Metric import functionality we can now build a metric in a Pigment application that will hold today's date and will update automatically each night.
In this article we will explain how to set this up. You can use the final metric for multiple calculations or as a variable to automatically update the page selectors of your boards. A few examples of use cases are listed below:
- Update page selector with a dynamic variable connected to the Today metric
- From the Today metric you can calculate a current month metric and connect that to your application variables. This will enable you to automatically update board page selectors or calculated items which will always show the current month.
- Calculate with time
- In some use cases you want to see a reference to today. For example you would like to show how much you are overdue on a project end date or you want to calculate the amount of days that an employee is working. With the today metric you can calculate this easily and it will update automatically.
- Filter in datasets
- Another use case is to filter datasets. Maybe you only would like to see the most recent 3 months of transactions? Leveraging the today metric will allow you to do that without any manual interference!
How to set this up:
- Create a dimension called Import and add 1 item called “1”
- Note that you can also use any other existing dimension as long as it has 1 item and will not change. We need this dimension to be able to run an import in step 4 as we can’t import into a metric without any dimensions.
- Create a date formatted metric with the name Today and add the dimension Import to it
- Fill the cell manually with yesterday's date (we will update the date to today's date in step 4)
- Create a date formatted metric with the name Tomorrow and add the dimension Import to it
- Apply the following formula: Today +1
- Set-up a Metric to Metric import from Tomorrow -> Today
- Make sure you save the import configuration and choose to clear existing values
- Schedule the import to run every night at 00.01 on your preferred timezone.
- Set yourself or any other admin user as the designated user to receive an email in case there is an error message on the import
All set! Now your today metric will always contain the current date and you can build any type of calculation or report based on it! If you want to reference your today metric in another block make sure you remove the Import dimension by using the REMOVE modifier in your formula. An example below shows how to calculate the current month by using the formula Timedim: timedim(Today [remove first: import], month)
