Using Qlikview’s Date Functions
Business Intelligence, QlikView
When putting together a data model for a Qlikview application you will occasionally run into a field that is not formatted properly, both from a numeric and presentational perspective. Behold the transformed junk file that Qlikview organized into meaningful data.
Meaningful yes, recognizable by the end user…not so much. Don’t get me wrong, it all works as you can see from this example.
Selecting a date will match up with the Plant #, Production Line ID, and Estimated Production, but the average user doesn’t what to stare at the date value and disassemble so they can mentally reassemble it as a logical date.
That’s where Qlikview’s Date() function steps in, or is it the Date#() function? You see, they are both legitimate, the first function is a formatting function and the second is an interpretation function.
A formatting function takes a numeric value as input and converts it to text whereas an interpretation function uses text as input and converts it to a number. Either way, the output is a dual which means it can be displayed as text but utilized as a number.
The first thing we’ll need to do to make it recognizable as a date is use an expression instead of the lonely date field. Right clicking the Listbox and choosing Properties from the context menu will let you change the field associated with the Listbox…or build an expression using the field.
The Date#() function will allow us to convert a string such as 20120528 into the serial date number 41057. Qlikview uses a serial date number based on December 31, 1899. 1/1/1900 being day 1, 1/2/1900 being day 2 and so on for each day. Today’s date 9/28/2016 would be day 42641. The format ‘YYYYMMDD’ tells the function what part of the number represents what part of the date. By the way, the word Date (in red) after the opening parentheses represents the actual date field.
Next we’ll nest the existing Date#() function within the Date() function. It will also be supplied with a format to present the date in a recognizable manner.
Our completely formatted date is now not only usable, but also recognizable courtesy of both Qlikview date functions.
