Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

Few things are as important to understand as the concept of nothingness. Or, rather, the fact that there are always many levels of nothingness.

 

In physics, vacuum is the word used for nothingness. But whereas the best vacuum on earth contains billions of molecules per cubic meter, vacuum in outer space contains fewer than a dozen. So, these two vacua are completely different. And neither is really empty.

 

What if we find some space completely void of molecules? Would that represent nothingness? No, because the space would still be traversed by force fields, e.g., gravitation from distant stars. But space void of force fields, then? No, you would still have vacuum fluctuations, a quantum mechanical effect that can create particles from nothing. True nothingness may perhaps not exist. But one thing we know for sure is that there are levels of nothingness; one vacuum is not the same as the other.

 

In Lund there is a statue of Nothingness (Swedish: “Intighet”). There is nothing there, except the void of statue. But the statue’s existence is shown by a small plaque in the ground.

 

00398200.jpg

 

To complicate matters, there is a second plaque some centimeters away that announces that the statue has been stolen. The two plaques illustrate both the sense of humor in the student city of Lund and the universal existence of different levels of nothingness.

 

In databases and in QlikView, NULL is the word used for nothingness. But this is not the only type of nothingness. Also here you have different levels:

 

  • The simplest representation of nothingness is the 0 (zero). But this is not true nothingness, since there is a numeric value in the field. The value will be used for calculations, e.g., it will affect both Count() and Avg(). So it is certainly not NULL.
  • Another level of nothingness is the empty string. This may not be as obvious, but also this is a field value that affects the calculation of Count(). Hence still not NULL.
  • The next level is the true NULL. This is when you have a record in the database, but there is no value for the specific field. This cell in the table is marked as NULL, meaning “a value is missing here.”
  • The final level is when the entire record is missing. An example is if you have a customer table and an order table and a specific customer has not placed any orders. Then the customer is not represented in the order table and there is no table cell that can be marked as NULL. These are called Missing values and are treated the same as NULL values – when possible.

 

If you want to present data in a correct way and at the same time enable the user to search for missing values, e.g., customers that have not bought a specific product, you need to understand the different cases of nothingness. Nothing could be more important.

 

HIC

 

More on nothingness:

NULL – The Invisible Nothing

Finding NULL

Excluding values in Set Analysis

Also, see more about this topic in this Technical Brief: NULL and Nothingness

8 Comments
Or
MVP
MVP

Dealing with "Nothing" is indeed a critical component of BI in general and business discovery in particular. I'd be a happier designer if QlikView did a better job of dealing with nothing in some contexts, most notably line charts. QlikView is one of the few BI tools out there that insist on connecting adjacent dots even if there is a null value between them (see image below) - most BI tools are now capable of displaying a non-continuous line in these situations.

MissingDay.png

As this image clearly shows, there is no value for Wednesday, but anyone looking at the graph without "Value on Data Point" would assume Wednesday's value was 15 because of the line connecting Tuesday and Thursday. While workarounds are available, this should really be offered as a simple, native functionlity.

Understanding nothing is important - but without the ability to correctly display nothing, you'll never know it's there.

7,398 Views
hic
Former Employee
Former Employee

I can only say that I agree with you. The request is logged as a development request and I will push for it.

HIC

7,398 Views
Not applicable

I like your examples of levels of nothing and I would like to add some others:

1) The basic type of division of nothing can be denoted like EXPLICIT versus IMPLICIT nothing.

2) The standard sorts of explicit type is zero, blank string, or NULL value.

3) The sorts of implicit type are for example:

3a) Entire record is missing (as you stated) and this fact is realised by someone (as I add to it). I think you can hear here Socrates.

3b) Entire record is missing (or other examples) and this fact is reflected by nobody. This is standard situation of our thinking, I am afraid.

But now I give some type of NULL (like some sorts of explicit type of nothing) which is not connected so closely to QlikView but more to ETL processes, namely to SCD (slowly changing dimension). It will be NULL values in columns for foreign keys in fact tables which may link to dimension tables, but they actually link nowhere (they contain NULL).

So from this point of view we can distinguish for types of NULL value:

1) NOT APLICABLE: this NULL value if for cases in which there is no suit to fill in some concrete value inside some column or field, for example:
  1a) employee number in table of all people connected to some firm: customers, employees, providers etc.
  1b) code of father (at recursive defined hierarchy) for the top of it.

   It is correct situation.

2) NOT HAPPENED YET: this NULL value will suit into some column but just now it is not its time. For example if we have fact table of accumulating time snapshot type (no traditional transaction type) where we write calendar date of some events and we have here date for event #1, #2 and just now ve have no calendar date for event #3 which will happen in (near) future.

   It is correct situation.


3) MISSING VALUE: this NULL value indicates that in product system is missing any value and this is error.

4) BAD VALUE: this NULL value indicates that in product system exists some value,
but it is bad value, because it does not exists in relevant dimension table.

Best regards,

Mirek

0 Likes
7,398 Views
Not applicable

Good point, but what if I want to show the average?

If I have a calendar for weekday and I set it up so it calculate from Monday and till the day I choose, so if I choose Tuesday its (10+15)/2, but what If I choose Friday in the above scenario?

Would you divide with 4 or 5?

What if I want to be able to choose Wednesday and still get the result (10+15)/2? would we then destroy the basic Qlik functionality?

0 Likes
7,398 Views
Or
MVP
MVP

This is a simple answer: Unless specified otherwise, your result is sum(Values)/Count(Values). Nulls are not on either side of the equation.

If you want to consider null values 0 for the purpose of averages, you would likely replace them with 0s using isnull().

0 Likes
7,398 Views
Or
MVP
MVP

Following up, it's been four years since this post and QlikView still draws lines over null values. I know QlikView development has taken a back seat recently for Sense development (I don't know how Sense handles null values in charts), but I really wish this sort of thing would eventually get to the top of the to-do list.

0 Likes
7,398 Views
hic
Former Employee
Former Employee

The behaviour of charts when the measure has no data points and the axis is date/time can be discussed. In QlikView line charts, lines are indeed drawn over data points with missing values, which is a non-optimal behaviour.

Basically, what you want to do depends on which measure you have. There are three alternatives (and this ought to be exposed in a user setting per measure):

  • Draw the line at zero when there are missing values (usually what you want if the measure is Sum() or Count())
  • Extrapolate the line from existing values - so you get a "guessed" line over the missing values (usually what you want to do for warehouse balances and some KPIs)
  • Use a gap in the line (the "strict" approach)

With these options, we should cover all cases...

0 Likes
6,065 Views
Not applicable

orsh_ :

1. Yes, I believe that the avg() function does this, but with the other problem it is more complicated:

2. The problem is that you cant just replaced with 0, since it it a value too. Why not replace with like 1, 5, 10 or -478236? What if the scale is between 10 and 20?

My point is that 0 is not more "innocent" or neutral than any other number, its a value too. What if it is Interval vs Ratio scale, what difference does it make?

You can take different approaches, replace with 0 as you suggest or if the scale is 10-20 you might say 15, but some might argue that it should be the average from the mni/max, or that replacing with an average for the last 3 month would be more correct. How would you do that? 

If you need to display for a specific subject, the 3 month rolling, but for the third month for this subject there is no numbers/scores whatever, that is you can't select this month from the master calender when the subject is selected, but you need to select this month to see Month Rolling. So you need to destroy how QV Works?

Edit: I made a blog for this: The average of nothing?

0 Likes
6,065 Views