Summary: I review a subtle difference between using Num() in the script vs Num() in charts. I make mistakes so you don’t have to 🙂
2020 marks my 13th year blogging about Qlik. I’m still learning, and still making mistakes! I’ll review a problem I created for myself recently, with the hope that you won’t have to repeat the exercise.
Here is a simplified example of the problem, enough to demonstrate the issue: This is the starting data table.
We have some number of “Metric” and “Value” along with fields that describe how the data should be formatted for display. The “Format” field contains a Qlik format specification. Format may be used in a Num() function as shown in the “Num(Value, Format)” measure in this table. Output is as expected, so far so good.
In my project, it became required to format the Value in the script. I moved the Num() measure to the script like this:
Num(Value, Format) as ValueFormatted
I expected ValueFormatted to yield the same result as the chart measure. Adding “ValueFormatted” to the chart yields this:
The results are not the same. ValueFormatted for “Days to ship” is incorrect. The other values are correct.
Let’s introduce another variation. This time I’ll sort the input when I create ValueFormatted.
Left Join(Data) Load *, Num(Value, Format) as ValueFormatted Resident Data Order by Metric Desc ;
Now “Customers per location” is incorrect and the other values are correct! What gives?
The Num() function returns a Dual() value . Duals have both a string (display) and a numeric value. When populating a data model field, Qlik will use a single string representation for a given numeric value for that field. The string selected will be the first encountered for that numeric value.
“Customers per location” and “Days to ship” shared the numeric value 4. In the data model, one or the other string representation — “4” or “4 days” — will be used, depending on which one is created first.
To get the correct results in this scenario — that is, unique strings dependent on Format — add the Text() function to extract the string at runtime.
Text(Num(Value, Format)) as ValueFormatted
Resolution came of course after I took the advice of my colleague Barry to “take a walk”.
I hope my story might save you some trouble. Happy scripting!