Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Numbers and dates are a never-ending source of concern when building any BI solution.

But in QlikView, there is always a way to solve a problem… First, QlikView can interpret and format numbers and dates using functions in the script:

Number Format in Script.png

Alternatively, it can format them in the user interface, where each object, dimension or expression potentially can have its own number formatting:

Number Format Dialog small.png

The common denominator for interpretation and formatting in QlikView is the Format Code. QlikView uses it as a parameter in many functions, and you can find it in the Format Pattern on the Number tab in the properties dialog.

Some rules of thumb around the format codes:

  • Unrecognized characters in the format code can prevent QlikView from interpreting a number.
  • The codes are case sensitive. For instance, M means month, whereas m means minutes. Some symbols can be written in either upper or lower case, e.g. AM/PM (or am/pm) and XIV (or xiv). For these, the format code must correspond to what you want: TT for AM/PM; tt for am/pm; (ROM) for XIV and (rom) for xiv.
  • With the format code you can force a rounded display, e.g., by specifying two decimals on a multi-decimal number or by specifying year and month, but not day, when displaying a date. Such a rounding will only change the display and not the underlying number. To change the numeric value, you need to use a rounding function, e.g., Round(), Ceil() or MonthStart().

You can specify Integers to have leading zeros. You can also specify them as binary, octal, decimal and hexadecimal numbers. In fact, you can use any radix from 2 to 36. You can also format them as Roman numerals.

    Num( Number, '000000' ) as Number // Always with at least 6 digits. Leading zeros!
    Num( Number, '(R36)' ) as Number // Displayed in Radix 36 (like hex, but with 36 symbols)
    Num( Number, '(ROM)' ) as Number // Upper case Roman numerals

See picture below for the different cases. Each column denotes a specific format code. The rows are examples of input numbers and how QlikView will format them.

Integer formatting.png


Float numbers are similar. If you need QlikView to interpret a number that has a decimal symbol different from the one in the environment variables, be sure to use the third and fourth parameters of the Num#() function to specify decimal character and thousand separator. A correct format code is not enough.

          Num#( Number, '0,0', ',' , '.' ) as Num // Number with decimal comma and point as separator


Float formatting.png

QlikView interprets the integer part of a date serial number as a date – the number of days from Dec 30, 1899. Date formats are different from country to country so you sometimes need to specify which format you want. Note that you can specify weekday also..

          Date( MonthStart( date ), 'YYYY MMM' ) as YearMonth


Date formatting.png

QlikView interprets the fractional part of a date serial number as time of day. This can be specified in hours and minutes, etc. Note that the TT symbol denotes AM/PM. If this is not used, QlikView will assume 24-hour notation.

          Time( Ceil( Time, 1/24/4 ), 'hh:mm' ) as Time // Time rounded downwards to nearest 15 min


Time formatting.png

I recommend that you use interpretation, rounding, and formatting functions in the script to transform data into a form that you want.

HIC

Further reading related to this topic:

Data Types in QlikView

Get the Dates Right

QlikView Date fields

25 Comments
nivellen11
New Contributor II

Usefull tips but i still dont kwon how to convert 19 characters string ( first char is a 0 or - for negative value) to number:

Example:

0000000000004964,66

-000000000146021,00

0000000000022940,97

-000000000001000,00

-000000000000013,00

Expected output:

    4964,66

-146021,00

   22940,97

   -1000,00

       -13,00

thanks for help.

0 Likes
1,880 Views

QlikView cannot format numbers with more than 14 digits. And it cannot automatically interpret strings that long either.

But there is a work-around: The function Evaluate() can be used for an evaluation that goes beyond normal number interpretation.Try the following:

Load Evaluate(Number) as Number inline

[Number

0000000000004964,66

-000000000146021,00

0000000000022940,97

-000000000001000,00

-000000000000013,00

pi()

5*6];

HIC

1,880 Views
nivellen11
New Contributor II

Yeah, THX for help. i use somthing like this and it works :

LOAD Evaluate(Left(@3,16))+if(Left(@3,1)='-',-(num#(Right(@3,2))/100),(num#(Right(@3,2))/100)) as ny_number

0 Likes
1,880 Views
Employee
Employee

@hic, is there any format_code to show week numbers?

I couldn´t find the complete list of tokens and format inside QlikView Documentation

1,880 Views

cleveranjos

No, unfortunately there is not. You will have to create it by using the week function and string concatenation.

HIC

1,880 Views
gautik92
Valued Contributor III

very helpful

0 Likes
1,880 Views
Not applicable

good

1,880 Views
Not applicable

Hi HIC

I have a field which has values

0.98981891628932,0.010181083710677 .. when I add up these numbers I get results as 1 in qlikview where as it is 0.999999999999997.I have to enable a flag when the sum results in integer(so when this value is rounding to 1 my flag is failing).So finally even when the number has 15 decimals I dont want to see rounded value

Regards

Anu

0 Likes
1,880 Views

The internal number representation only has 52 bits, which corresponds to 14 digits. This means that you can get rounding effects like this when you have 14 or more digits. And you should not have equality (equality with an integer) as condition for a flag.

See Rounding Errors

and Double-precision floating-point format - Wikipedia, the free encyclopedia

HIC

0 Likes
1,880 Views
MVP
MVP

Henric,

I've just noticed that the number interpretation of binary values using

NUM( NUM#( BinaryField, '(BIN)'))

seems to be working only for BinaryField values with up to 14 digits, which sounds kind of familiar, but I think it's a bug in this case. Any thoughts on this issue?

Regards,

Stefan

P.S: I miss your Qlik Design blog posts

1,880 Views

What would the bug be? It is known limitation that an integer number with a mantissa larger than 52 bits (see binary64) cannot be interpreted using Num#(). This is true irrespective of whether it is in binary or decimal representation.

And, yes, I should write more blog posts... If you have topics that you want to suggest, feel free to do so.

HIC

0 Likes
1,880 Views
MVP
MVP

I am referring to the discussion in this post:

Converting binary sequence to decimal

It seems like a bug to me that 000010111101011101111010 which converts to the decimal value 776058 can't just be interpreted using num(num#('000010111101011101111010','(BIN)')), but needs some work around.


------------------------------------------


Suggestions for your blog topics:

I recently came across some discussion that talked about temporary tables in the script, like MAPPING tables, or internal tables used for Autonumber*(). The discussion went about memory consumption of the tables, if it's possible to somehow control QV to drop e.g. a MAPPING table and if AutonumberHash*() is superior to Autonumber() in terms of memory consumptions. Maybe different topics, but the mysteries of the internal, temporary tables might need discovery.


Also any other insight to more technical details would be great, for example, more details on how QV handle different states / set analysis set modifications. And most important the overhead of using these.

1,880 Views

Of course you are right... I wasn't aware of this.

So, to summarize: Basically, the following expression works fine:

     Num(Num#('00001011110101','(BIN)'))

but the following does not, since it has more than 14 digits:

     Num(Num#('000010111101011','(BIN)'))

Totally unnecessary, so, yes I would call it a bug.

HIC

0 Likes
1,880 Views
Partner
Partner

Great ideas for blogs!

0 Likes
1,880 Views
Partner
Partner

Hi hic‌,

Edge case question: Is there any way to escape a character in a format code?

In a perfect world I'd like to be able to format an interval as 'T+D hh:mm:ss', e.g. T+3 12:00:00, but of course T is reserved for am/pm so that doesn't work.

1,880 Views

Unfortunately not. But we are aware of the problem... The work-around is to use string functions to interpret or format.

When we designed this many years ago, the ISO format was '2007-04-05 14:30', but it has since been changed to '2007-04-05T14:30', with the option of an additional Z (for zulu time) or +03:00 for time zone. The "new" format is recognized in the automatic interpretation, but there is no format code for it. Feel free to contact me if you have a suggestion.

HIC

0 Likes
1,880 Views
Partner
Partner

OK, fair enough, thanks for the swift reply.

Just to confirm ... if it's an axis in a chart (line chart in this case) then I can't use string functions to format (or at least, the axis labels won't display it how I've formatted it, but the popup will). I can live with just 'D hh:mm:ss' I just want to make sure I'm not missing a trick that would let me get 'T+D hh:mm:ss'.

I can't see how to attach my dummy app so I'm attaching an image:

interval example.png

0 Likes
1,880 Views
Partner
Partner

Computed Dimension

0 Likes
1,880 Views
MVP
MVP

Try an interval format code something like

Interval(<exp>,'T+D hh:mm:ss')

(note: first character is Unicode U+FF34 fullwidth capital letter T)

Unbenannt.png

1,880 Views
Partner
Partner

sneaky ... thanks swuehl

0 Likes
1,880 Views
Partner
Partner

The Unicode character probably works with Date() also.
But why not simply put the T infront?
'T' & Date(<date>, '+D hh:mm:ss')

0 Likes
1,880 Views
Partner
Partner

You can do that fine in a table or a text object, but that won't work when it's an axis. See my earlier comment. Happy to send you the QVW if you like.

0 Likes
1,880 Views
Partner
Partner

Yes you can by using a Calculated/Computed Dimension.
(don't remember what it's called in English)

0 Likes
1,880 Views
Partner
Partner

Hello Henric, hic

I was referred to your article which is great by the way, thanks.

I read financial instrument data in a QlikView application and have a scenario what a INSTRUMENT with the code R207 is interpreted in a weird way by QlikView.  You can maybe refer to my question: QlikView changing text value

Am I correct in saying that, this is just the way things work and one has to do a text conversion on such fields@, always ?  So this is not a defect ?

0 Likes
1,880 Views
qlikviewwizard
Honored Contributor II

Hi hic

This is giving perfect results.

Load 1*Evaluate(Number) as Number

inline

[Number

0000000000004964,66

-000000000146021,00

0000000000022940,97

-000000000001000,00

-000000000000013,00

pi()

5*6];

0 Likes
1,880 Views