Is it possible to incorporate a custom attribute in the form of a placeholder in a calculated element’s expression? For example, to convert date/time values from UTC to the user’s browser time zone.
Using custom attributes in data cube calculated elements
It is possible to use a custom attribute to convert the time for every user based on their time zone. At the moment it is not possible to add a placeholder in a calculated element, they are only available for manual selects. If you use a manual select in your data cube, you can define the placeholder there. If not, you can create one that contains only the placeholder and a join id to bring the placeholder into the calculated element. The setup below goes with the latter option.
First, you need to set up a custom attribute that contains the time zone of the user. This can be a string or a number. In the below example, the custom attribute is an integer that defines the offset of the user’s time zone compared to UTC. For example, a user with the time zone UTC -4.
Next, create a data cube that contains a Select transform with the data that you want to show the user. Add a Manual select, define the placeholder ($timezone$), and choose the custom attribute as the value for the placeholder. For the example setup, also add 1 as joinID.
Configure the Select transform by adding an expression that returns 1 to be able to join the two tables.
Use a Join transform to join the tables and then add the Calculated Element.
The resulting cube looks as follows:
Open the Data Preview panel to see the result. In this example, the date coming from the Calculated Element is 4 hours behind the original ModifiedDate.
For more information, please check the articles about Using custom attributes to filter data by user and Using manual select placeholders.
1 Like