When developing reports and dashboards, users will often mention the most ambiguous word of them all, ‘today’.
‘I want today’s numbers,’ is all too familiar. It may seem simple but what really constitutes as ‘today’? Is it actually today, or is it the last full day, and therefore yesterday? Or quite often today will be interchanged with ‘latest’ but they’re not the same. The latest numbers could be dated by a week, month or older, but they are still, quite rightly, today’s numbers as the data may only be captured monthly, for example.
As you can imagine, or may have experienced, today is a term used by countless users wanting to report on a collection of measures based on a frequently changing point in time. As a result, I always clarify this term as soon as it is mentioned. In my experience, it can be defined in several different ways and the answer is mostly dictated by the data; namely, how frequent the data is updated.
What impact can the wrong definition have?
If a developer has designed an application around today, they may have used the TODAY() function – which returns the current date based on the server’s regional settings. The issue may become evident when an application, or its data source, is not updated on the current date. Any load queries or visualisation expressions limited to the current date will therefore show no data.
Choosing the right definition.
The chart below highlights different definitions for ‘today’ I have come across:
This application was last reloaded on 25/09/2018 (green). As I write this blog the date is 18/10/2018 (orange), notice the data hasn’t been updated past 02/10/2018 – the max date (blue). Therefore, any expression reference the TODAY() function will return zero values, hence there is no orange bar are in the visualsiation above.
Applying the right definition in Qlik Sense.
Using the TODAY() function in some cases is correct, and/or won’t have any repercussions. However, more often than not, I see it used without proper thought and issues emerge. One common issue I come across is when users build a new application without considering when the reload will be scheduled.
For example, if load statements are referencing the TODAY() function and are set to reload at 11pm at night, then when users access the app in the morning, the data is only as fresh as yesterday, and not today. Therefore, referencing TODAY() in any visualisation expressions may not work as expected. One poorly implemented application I happened to come across was when the developer had referenced the TODAY() function in all load scripts, but the administrator set the report to run some time before midnight, and didn’t end until well past midnight (there was a lot of data). The fundamental mistake here was that you have different load scripts within the application referencing different days, as the value returned from the TODAY() function changed during the course of the reload.
People often don’t realise, or forget, that you can pass parameters through to the TODAY() function. Let’s look at some of the more useful examples:
1. TODAY() – As previously mentioned, returns the current date.
2. TODAY(0) – By passing the parameter zero (0), Qlik will return the last reload date.
3. TODAY(1) – When used in a visualization expression, this returns the day of the function call. However, when used in a data load script, this returns the day when the current data load started.
4. MAX(TOTAL Date) – When used in a visualisation expression this will return the maximum date, within the field values.
So, personally, when developing in the Data load editor I will use TODAY(1), as this is the safest option there is. Even if your script runs in a couple minutes or you have enough contingency right now, always be a belts and braces developer. Alternatively, you could store the value in a variable at the beginning of the script, however I believe this is unnecessary unless you are transforming the date as it makes the script less readable.
In visualisation expressions, I’d rarely use the TODAY() function, in any variation, as the reference for the current date. Instead I’d typically opt for MAX(TOTAL Date) – using the data to reference the current date. Having said that, if the data contains future dates, for budgets, forecasting, future orders, etc. then I will use a variation of the TODAY() function or flags within the data to limit that expression to appropriate date values.
For example:
MAX({1} TOTAL Date)
In summary, building applications requires a developer to gather some data before they can visualisation it. Below is a non-exhaustive list of a few questions I would ask around the timeliness of data:
– How recent does the data need to be?
– Does completeness matter? For example, does the user only want a full day’s data or does the user require near real-time.
– Are we including budgets/forecasts or any data with future dates?
– What the rate of velocity of the source data?
If you would like more information of things to consider when building a Qlik Sense application then please take the time to follow us on Twitter and LinkedIn, or feel free to get in touch.
By Christopher Lofthouse