Source: http://community.qlik.com/blogs/qlikviewdesignblog/2013/07/07/automatic-number- interpretation
I have in several previous blog posts written about the importance to interpret dates and numbers correctly e.g. in Why don’t my dates work?. These posts have emphasized the use of interpretation functions in the script, e.g. Date#().
But most of the time, you don’t need any interpretation functions, since there is an automatic interpretation that kicks in before that.
So, how does that work?
In most cases when QlikView encounters a string, it tries to interpret the string as a number. It happens in the script when field values are loaded; it happens when strings are used in where- clauses, or in formulae in GUI objects, or as function parameters. This is a good thing – QlikView would otherwise not be able to interpret dates or decimal numbers in these situations.
QlikView needs an interpretation algorithm since it can mix data from different sources, some typed, some not. For example, when you load a date from a text file, it is always a string: there are no data types in text files – it is all text. But when you want to link this field to date from a database, which usually is a typed field, you would run into problems unless you have a good interpretation
algorithm.
For loaded fields, QlikView uses the automatic interpretation when appropriate (See table: In a text file, all fields are text - also the ones with dates and timestamps.) QlikView does not use any automatic interpretation for QVD or QVX files, since the interpretation already is done. It was done when these files were created.
The logic for the interpretation is straightforward: QlikView compares the encountered string with the information defined in the environment variables for numbers and dates in the beginning of the script. In addition, QlikView will also test for a number with decimal point and for a date with the ISO date format.
If a match is found, the field value is stored in a dual format (see Data Types in QlikView) using the string as format. If no match is found, the field value is stored as text.
An example: A where-clause in the script:
Where Date > '2013-01-01' will make a correct comparison
The field Date is a dual that is compared to a string. QlikView automatically interprets the string on the right hand side and makes a correct numeric date comparison. QlikView does not (at this stage) interpret the content of the field on the left hand side of the comparison. The interpretation should already have been done.
A second example: The IsNum() function
IsNum('2013-01-01') will evaluate as True IsNum('2013-01-32') will evaluate as False
In both cases, strings are used as parameters. The first will be considered a number, since it can be interpreted as a date, but the second will not.
A third example: String concatenation
Month(Year & '-' & Month & '-' & Day) will recognize correct dates and return the dual month value.
Here the fields Year, Month and Day are concatenated with delimiters to form a valid date format. Since the Month() function expects a number (a date), the automatic number interpretation kicks in before the Month() function is evaluated, and the date is recognized.
A final example: The Dual() function
Dual('Googol - A large number', '1E100') will evaluate to a very large number
Here the second parameter of Dual() is a string, but QlikView expects a number. Hence: automatic interpretation. Here, you can see that scientific notation is automatically interpreted. This
sometimes causes problems, since strings – that really are strings – in some cases get interpreted as numbers. In such cases you need to wrap the field in a text function.
With this, I hope that the QlikView number handling is a little clearer. 14. Why don’t my dates work?
Source: http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates- work
A common recurring question on the QlikCommunity forum is around dates that don’t work. Here follows a help on fixing the three most common causes. If you encounter such a question on the forum, just link to this post in your answer.
1. Incorrect Date Interpretation
When data is loaded into QlikView, dates are often read as strings. QlikView then tries to recognize a pattern in the string that looks like the date format specified in the DateFormat environment
variable. This sometimes fails and then you need to use the Date#() function to help QlikView understand that it is a date.
How do I know that a date is correctly interpreted? That’s easy. Just format it as a number and see what you get. (List box properties – Number – Integer)
The question is now what your list box looks like. If you have a number which is roughly 40000 (usually right-aligned), then you are all set. But if you still have a date stamp (usually left-aligned), then you need to use the Date#() function in the script. See QlikView Date fields.
2. Linking integer dates with fractional dates
You have a date in two different tables, and you want to use this date as a key, but it doesn’t seem to work. Then you should suspect that you have true dates (integers) in one table and timestamps (fractional numbers) in the other, but the formatting of the dates hides this fact.
How do I know whether this is the case? That’s easy. Just format it as a timestamp and see what you get. (List box properties – Number – TimeStamp)
The question is now what your list box looks like. If you have timestamps where hours, minutes and seconds are all zero, then you are all set. But if you have numbers in these places, then you need to use the Floor() function in the script to get integer dates. See QlikView Date fields.
3. Incorrect date comparisons
The most subtle error is however the one with timestamps in comparisons, e.g. … Where Date = '2011-12-31';
Will this work? Yes, provided that the date format inside the string is recognized by QlikView, i.e. that it corresponds to the date format specified in the environment variable DateFormat in the beginning of the script.
It becomes even more complex if you use variables. Then it is important to use quotes correctly. The following will work:
Let vToday = Today(); … Where Date = '$(vToday)'; … but the following will not: … Where Date = $(vToday);
The reason is that the $(vToday) will expand to the date, and then the comparison will be e.g. … Where Date = 2/19/2013;
So the date (which is approximately 40000) will be compared to 2 divided by 19 divided by 2013, which of course is not what you want.
My recommendation is to always use numeric variables for dates. They always work - quotes or no quotes:
Let vToday = Num(Today()); … Where Date = $(vToday);