Thursday 26 June 2014

QlikView Funtions: today() and now()

In this post I want to take a look at two very closely related functions, today() and now(). Many of the more advanced calculations that we want to perform with dates and times require us to know what date it is, and what time it is.

First things first, let us take a look at what the help says about these two functions:

today([timer_mode] )
Returns the current date from the system clock. The timer_mode may have the following values:
0 Date at script run
1 Date at function call
2 Date when the document was opened
Default timer_mode is 2. The timer_mode = 1 should be used with caution, since it polls the operating system every second and hence could slow down the system.

now([timer_mode] )
Returns a timestamp of the current time from the system clock. The timer_mode may have the following values:
0 Time at previously finished reload (not currently ongoing reload)
1 Time at function call
2 Time when the document was opened
Default timer_mode is 1. The timer_mode = 1 should be used with caution, since it polls the operating system every second and hence could slow down the system.

We can see from this that there are only really two differences between the two functions. The first is the format of what they return. today() returns a dual value representing the date taken from the system clock on the machine where QlikView is running. The string part will be formatted inline with the format set in the DateFormat system variable within the script. now() also returns a dual value taken from the system clock, but it is formatted as a timestamp inline with the format set in the TimestampFormat system variable.

The second difference is the critical one, the functions' default timer mode. First let's consider the different timer modes.

Timer mode 0 makes us consider that both functions can be used in both the script and expressions. In expressions it returns the logical result, the date/timestamp when the script last finished executing, but when using timer mode 0 in the script, some people get confused. Timer mode 0 will return the date/timestamp when the last script execution finished, not as some assume, when the current script execution was started.

Now I know this is out of order but let's look at timer mode 2 first, why will become clear in a second. Timer mode 2 is simple, it returns the date/timestamp when the QVW was opened. Not a lot else to be said about it.

The help for both functions includes a warning that timer_mode 1 should be used with caution. This is because timer mode one returns the date/timestamp when the funtion is called. This doesn't sound dangerous in itself and when using either function in the script this causes no problem at all. But when using them within expressions, timer mode 1 causes a major issue. Because the time within the system clock changes every second, QlikView assumes you will want it to recalculate the expression every second. And because one expressions changing can have a knock on effect if other expressions are referencing it, QlikView will actually recalculate every expression in every chart and object currently visible to the user. As you can I'm sure guess, the impact on the usability of the application is huge and can most often result in the application becoming unusable.

OK so you are thinking, I'll just never set the timer mode to 1, but remember I said the second difference between now() and today() was their default timer mode. For now() the default timer mode is 1 and so if you use the now() function in an expression and don't specify the timer mode, you can very easily be causing yourself a huge problem for your application.

As a general rule, always remember to never use the now() function without setting the timer mode!!!

No comments:

Post a Comment