Sign InHelp

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Documents & videos about Qlik Sense.

- Qlik Community
- :
- Qlik Product Forums
- :
- Qlik Sense
- :
- Qlik Sense Enterprise Documents & Videos
- :
- KPIs formatting by using class()

Options

- Article History
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Email to a Friend
- Printer Friendly Page
- Report Inappropriate Content

jamajka1

Contributor

04-21-2018
05:07 PM

KPIs formatting by using class()

If you build BI frontends you probably meet a challenge to visualize measures not knowing if they are in thousands or millions. Beacuse of that some posts about formatting these kinds of measures (above all because of KPIs) has been written already. I decided to spend some minutes on my own to find the solution and here it is.

**Problem we want to solve:**

- We do not know if the measure will be in hundreds, thousands or millions
- Final measure should consists of maximum 3 characters before and 2 after the decimal separator
- Final measure should contain unit of measure in text form

If you wonder only about the solution feel free to scroll to the end .

Let’s try to figure it out in logic steps (in case of this example we use simple sum(Value) as a measure):

1. We intuitively class numbers into units, thousands and millions. The difference between thousand and one is 3 more numbers. The difference between million and thousand is 3 more numbers, too. It is obvious that the crucial aspect is the length of a measure – in multiples of 3:

2. Since before the delimiter we want to have only up to 3 numbers values that consists of up to 3 characters (0-999) should stay unchanged, thousands (4-6 characters) should be divided by 1 000 and millions (7-9 characters) by 1 000 000. It seems as a work for class() function. However, if we use it just in simple way class(len(sum(Value)),3) the result is:

3. We can see that value 136 (which we do not want to change) is in the same class as value 9 582 which should be divided. This leads us to a small modification of the class function class(len(sum(Value))-1,3):

4. Class() function is dual so except for the text presentation 3 <= x < 6 we can refer to the left interval’s border by using another function num(class()):

5. The factor we want to use for dividing of thousands is of course 1 000 = pow(10,3) and for millions 1 000 000 = pow(10,6). Values smaller than one thousand do not need to be modified but we can divide them by 1 which is nothing else then pow(10,0). In general we have the function:

6. Let’s look at decimal places. As we focus on details we know that if the value has not been divided (and is the whole number) then we do not have to write anything after decimal separator (this depends on your data - if you have the raw data in decimals then you need to probably use only general function round(*,'0.01')):

7. By using the same logic as we define the power of 10 that should be used for dividing the value we can assign the text of corresponding unit to the measure:

8. When we add together everything we have done in previous steps our expression will look like this:

... it makes sense - qlik makes it ...

Maria

Comments

hassulka

New Contributor III

05-10-2018
07:26 PM

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Email to a Friend
- Report Inappropriate Content

Nice !

BTW have you seen the new system variable that allows to control numerical abbreviations for KPIs ? It's a useful feature for customization, e.g. :

SET NumericalAbbreviation = '3:k;6:M;9:B;12:T';

jamajka1

Contributor

05-20-2018
02:18 AM

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Email to a Friend
- Report Inappropriate Content

Yes sure and it looks great! Maybe there is almost the same algorithm as mine behind it .

pranavgohil

New Contributor III

05-21-2018
02:31 AM

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Email to a Friend
- Report Inappropriate Content

Heyy, check out the following discussion I tried something similar and that works with charts too

can we change sorting rule based on expression?

Version history