Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More

How to use - MissingCount()

How to use - MissingCount()

1f4fe19.jpgHave you ever wondered how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

MissingCount()

Description


Returns the aggregated count of missing values from expression or field iterated over the chart dimension(s). Missing values are all non-numeric values.

Count of all values (and NULL) which are  not a number.

Data model:

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

t1:

load * inline

[ sales,price,quantity,salesman

,15,10,Robert

20,,20,Henric

'',a,0,Robert

1,'',20,Oleg

50,' ',20,John

60,3,10,Tom

];

t2:

load

sales as Sales,

price as Price,

quantity as Quantity,

salesman as Salesman,

price*quantity as Stock

Resident t1;

Drop table t1

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

After reload and creating TableBox we can count all missing values in each column

Example 1:


MissingCount(Sales)

(+ few additional)

Example 2

MissingCount(if(Price>10, Price, 'invalid'))

The results of this expression (Yellow column) is either Number or Text character  returning

count  of values which are less or equal that 10.

(the first expression is for illustration only - showing results of  the IF statement.)

(We could achieve the same result by using =sum(if(Price>10,0,1)) 

Example 3

   Is important to mention that:

  • Emptiness can be represented by

            '' (two single quote), 

            empty space()

          ""  - two double quote

  • NULL is not a value - is an absence of value.
  • 0 and any (white)space are values.

(for more about nothingness and NULL please refer to this article NULL – The Invisible Nothing)

The Stock dimension shows how NULL values are obtained :

Although empty space is not a NULL multiplying it by  Text  or Number will return NULL)

Let's go back to our exercise:

Data for the Price column are as below

15

a

''

3

applying our expression:

MissingCount(distinct Price)   we are getting

                        


- Empty space and '' have been counted as one value

- Space

- a

(15 and 3 as numerical value has been omitted.)



Examples 4 and 5

MissingCount(Sales)/MissingCount(total Sales)


MissingCount(Sales)/MissingCount({1} total Sales)


The subtle but important difference here is use of {1}:

{1}- is an identifier represents the set of all the records in the application, will respond to a selection in dimensions.)


What does it mean?

If used in Text box  expression with {1} will not react to selection in dimensions

but if use in Chart it will consider that dimensions.

Please try to change values in Price dimension in the Script window to see how this will reflect the results.

Still feeling hungry?

How to use - Only()

Do you Qualify?- How to use QUALIFY statement

How to use - Dimensionality()

Missing Manual - GetFieldSelections() + Bonus Example

MaxString & MinString - How to + examples

The second dimension... or how to use secondarydimensionality()

Version history
Revision #:
1 of 1
Last update:
‎2015-03-20 10:29 PM
Updated by: