In some situations, you might need to calculate something based on the current date. The Metric to Metric import functionality allows you to build a Metric in a Pigment Application that holds today's date and updates automatically each night. This article explains 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 would only like to see the most recent three 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. You need this Dimension to be able to run an import in step 4 as you can’t import into a Metric with no 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 (you 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- p 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 in 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
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)