Skip to main content Skip to complementary content

Working with dates

This topic provides an example of how Qlik Application Automation for OEMcan keep track of dates and times.

Convert dates to timestamps

You can use - formulas in placeholders to convert date formats. Here's an example automation, where we read and show the last update timestamp from a record in a CRM:

an automation set up to output a timestamp.

an automation consisting of a Start block, a Get Contact By Email block, an Output show timestamp block, an Output show timestamp in epoch block, a Variable - days_difference block, and a final output block.

In the second Output block, we convert the date to a unix timestamp (epoch timestamp, number of seconds since 1-1-1970):

Outputting an epoch timestamp.

The second Output block. The Data to output field contains the date from the Get Contact By Email block.

In order to do this, we use the formula Date with output format U, which means a unix timestamp:

Editing a formula.

The Edit Formula popup. The Output format field contains the letter U.

This is the raw view of the same formula, applied to the date:

The raw conversion formula.

The Data to output field. Data to output: Timestamp in epoch: {date: $.getContactByEmail2.updated_at, 'U'}

Compare two dates

Once dates are converted to a timestamp (which is just a number), you can use a Condition block to compare the two dates. Here's an example where we compare the timestamp last update from a contact with a timestamp equal to now minus 5 days:

Comparing two dates.

an automation consisting of a Start block, a Get Contact block, and a Condition block connected to an Output block. The Condition block is selected. If the date from the Get Contact block is greater than now minus five days, the Output block is accessed.

Note: the formula for the timestamp of now minus 5 days can be added by clicking on the formula icon in the right top corner of the input field. Next, select the Date formula and enter now - 5 days for the value, and U for the output format (epoch timestamp).

Calculate number of days between two dates

Next, we are using a variable called days_difference of type number. In this variable, we will calculate the number of days between now and the above timestamp, in 3 steps.

First, we set the variable equal to the unix timestamp of now:

Setting the variable.

The Set value of days_difference dropdown. The value field is set to the unix timestamp meaning now.

Next, we subtract the previous timestamp:

Subtracting the timestamp.

The subtract from days_difference dropdown. The value field is set to Date > Get Contact By Email 2...Updated At,.

As a third step, we divide by 86400 (the number of seconds in a day):

Dividing the result by 86400.

The Divide from days_difference field. The Value field is set to 86400.

Finally, we round the result to get a whole number of days:

Rounding the result.

The Data to output field. It is set to Number of days: Round > Days Difference.

This is the raw view of the round formula:

Rounding the result, in raw code.

The Data to output field, as above, but written in raw code.

The overall output of this test automation would look as follows:

Output of the automation.

The automation's output, containing a timestamp, a timestamp in epoch, and number of days.