Working on a problem that uses the percent change formula:
percent change = 100 * [(new value - old value) / old value]
How would I explain the difference if new value or old value = NULL, rather than 0 to someone who might not be a programmer?
My boss is wondering why there is an empty string in the TextBox rather than a value, because we have the old value, but not the new value.
To explain to a boss the difference between "zero" and "null":
"Zero" is a value. It is the unique, known quantity of zero, which is meaningful in arithmetic and other math.
"Null" is a non-value. It is a "placeholder" for a data value that is not known or not specified. It is only meaningful in this context; mathematical operations cannot be performed on null (the result of any such operation is undefined, and therefore also generally represented as null).
For example, as in the comments: "What is your yearly income?" is a question requiring a numeric answer. "0" is a perfectly valid answer for someone who does not work and has no investment income. If the user does not enter a value at all, they don't necessarily make no money; they just didn't want to tell your software how much (or little) they make. It's an unknown, not specified; therefore, to allow the software to continue, you specify the "null" placeholder for that data field within the software. That's technically valid from a data perspective; whether it's valid at the business level depends on whether an actual numeric value (even zero) is required in order to perform a mathematical operation (such as calculation of taxes, or comparison with thresholds determining benefits).
In computers, virtually any operation on a variable containing null will result either in null or in an error condition, because since one of the variable's values is not known, the result of the expression cannot be known. The equivalent of performing math on null would be if I asked you "What's five plus the number I'm thinking of right now?". It's impossible for you to give a definite answer because you don't know the number I'm thinking of. An operation on zero, except for dividing by it, is usually valid and will return another known, unique value.
NULL.
Feb 13, 2012 at 21:24
NULL is the explict LACK of data, it isn't a placeholder, it is a single solitary representation of no data.
Boss-speak is always tough...
Zero is a number so you can do things with it.
Null is a unicorn. It doesn't exist so you can't do anything at all with it.
NVL(somefield,'UNICORN') ;)
Feb 13, 2012 at 20:50
Just rephrase the equations into sentences:
"What is the percentage change if you start with an unknown value and you end up with 150?"
and
"What is the percentage change if you start with 85 and you end up with some unknown value?"
Of course, the answer to both is "It can't be calculated, because one of the critical pieces of the calculation is missing." That's the essence of 'null'.
It should be easy then to figure out the equivalent sentences with zero, and see how they are fundamentally different:
"What is the percentage change if you start with an 0 and you end up with 150?"
and
"What is the percentage change if you start with 85 and you end up with 0?"
Although the answers might not make a lot of sense (with zeroes), at least it can be calculated. With null, the calculation is not even possible.
unknown it simply does not exist because in the case of a NULL percentage there isn't actually ANY numerical value(percentage) or otherwise percentage value.
When talking to you boss, just use 0 for zero, and ? for null. It correctly captures that it's a placeholder for something but that you don't know what it is.
How about this:
0 is the answer to "how much liquid is there in an empty bottle?". NULL is the answer to "what is the content of an empty bottle?".Or if you imagine a childless man:
0NULLThe basic difference is that 0 is about measurable quantity, while NULL is about existence. Being a quantity, 0 represents something, i.e. a quantity, that is 0, much like 0.000000001 represents a quantity (one that in problems of every day life is in fact indistinguishably close to 0). In contrast to that NULL represents nothing. In fact there's nothing close to NULL. Any variable's (and expressions) value is either NULL or something.
That's how I always successfully explain it:
0 is the number 0.
NULL is nirvana, nothing, nada, niente, non-existence, absence.
Arithemetical operations defined on NULL always yield NULL as a result.
... worked for me so far.
Why wouldn't you start by asking the question "why is the value equal to NULL?" That might help you explain why a calculation might be invalid.
If you were doing periodic updates, and the new value didn't present itself (network error for example), then NULL might mean the calculation can't be performed and the old percent value is stale. You might want to show a symbol indicating you have stale data, the update didn't occur as expected.
The same thing might be true for a missing old value, but I have a hard time understanding how an old value could be lost (unless this is the first calculation, or the battery died and the data was lost.) But you might want to show a symbol that indicates this condition as well.
In any case you don't want this to happen:
OldValue=Null you don't know the Old value so the Difference is Unknown (Null)
OldValue=0 You know the Old value, it is 0, so the Difference is Infinity (Null)
If its important to distinguish between these two, simply store the Old value as PreviousValue and display its somewhere.
Edit based on the comment below:
I would just ask him what he wants do in the following situations and show him examples of each of these. Then and ask him "What value do you want to display for these?"
Do not in any way make this a computer issue.. It's a business question.
I've had a lot of success with 'blank.'
With people that understand basic algebra (or the simple idea that you can assign a value to a symbol), the idea of a 'blank' value seems to be pretty straightforward and distinct from a zero-value.
NULL is not a value, its a lack of one. Zero is still a value. (even if it can be used in cases where it cant be fully evaluated.
By analogy (a good way of explaining to non teckies;
if I have three apples then I subtract three apples how many apples do I have? is 0
If I have a bag of apples and I never look in the bag, how many apples do I have? is null
Although in this case the two may have the same value (depending on your langage) they are conceptually very different
Really it isn't relevant whether your boss is a programmer. The issue is a conceptual one not a technical one.
Ask him to assume you got a raise. Your old salary was 175k, but your new salary is unknown. Then ask him - what percentage raise did you receive?
If he is arithmetically disabled, walk him through the process until he can see where the missing link is.
I use the coin flip. True is heads, false is tails, and one hand cupped over the other hiding the coin is NULL. If you know the coin is heads, it's very easy to answer the question "whats the opposite of this value?". One hand hiding the coin shows very easily that the person could answer if you'd let them see the current value, but since you won't, they can't give you an answer. For all they know, you don't even have a coin under your hand!
In a field, NULL says I have no idea; there's no data here -- its equivalent of blank. Zero (0) says I know the value of this field to be precisely 0, e.g., the integer that's one lower than the number one.
E.g., I could have a sheet of paper that I'm typing into the computer system that says how much each customer owes. Customer A owes $50, customer B owes $0, and customer C owes NULL (????), because the amount customer C owes number was redacted (someone drew a black line over it and its unreadable; or their was a note saying that their bill for isn't ready yet). I can comfortably say customer B owes $0, and can comfortably say I don't know what customer C owes. I don't want to charge customer C $0 (because they may owe money).
Assuming we're not getting into language-specific stuff like:
#define NULL 0
The difference is that 0 is a numeric value where NULL is not. It is like an empty cell on a sheet of graph paper. A cell can have a "0" in it, but it can also be empty. Null is still a value, but you have deemed it special. For example if your cells were now characters, you may use the empty cell as a space and a null cell is no longer possible unless you designate or create a character to be the special case.
What null means depends on how the the data is interpreted:
NULL == 0 works fine when you're dealing with null pointers in C et al. If you're trying to explain the idea to your non-programmer boss by showing them code, you're doing it wrong.
Feb 14, 2012 at 0:43
I would run NVL check and if one of the parameters is NULL I would display the String "N/A" (don't return "0" since it's just not true!).
This is something that, from a manager point of view, would look more professional then displaying NULL value which kind of looks like you have a bug (which of course you don't - but that's the way it look to a non-engineer).
we have the old value, but not the new value.
Seems like in his mind, not having a new value doesn't mean it is unknown; it is the same as the old value. You can code that accordingly without explaining null because in his mind, it's never null.
Instead of arguing the point about NULLs, just make sure this doesn't create other problems. What are the ramifications of having data that are too old? When someone sees a 0% change, do they hit the panic button? Will this skew any analysis or aggregates over time?
I just don't think he needs a computer programming tutorial, but it would be nice if he knew what a null was and asked the right question instead.
If the old value is null the result of the equations should rightly be null. This is because null is not a number, it means unknown value. Therefore the calculation cannot proceed.
Of course your equation also fails if old value = 0 as you cannot divide by zero.
Typically you handle these things with a case statement that will take you the path of what you want to show. So if you want the result to show as null when you can't calculate (as with a null or a zero, then you would do a case statement something like
case when [old value] = 0 then null
else 100 * (([old value] - [new value]) /[old value])
end
Some great discussion on analogies to explain the mechanical aspects of a null symbol compared with other values; however, the crux of this question perhaps from the manager's perspective in adequately expressing the results in the UI, representing the meaningful limits of the perecnt changed formula as applied to their specific problem, can be answered more specifically I believe.
In the case of the percentage changed forumula, two things need to be represented: when something is first measured, and when nothing is measured for a particular aspect.
When something is first measured (oldValue === NULL), the percentage change is not applicible; and could/should explicitly be noted that it is the initial measurement for that measurement cycle.
When nothing is measured (newValue === NULL), the actual resulting percent change is actually the value of zero (assuming non-null in the previous cycle). Zero is generally the appropriate answer to the formula in this case. There was no measurement in this most recent cycle, and thus no measured change. That should not imply it did not change, just that nothing was measured, recorded, or communicated in regards to that aspect.
Certainly they understand the concept of putting "N/A" on a form when they mean "not available". For instance, to answer the question, "How old is Benjamin Franklin?" the answers 0 and N/A are obviously different (and the latter one is correct). Similarly the answers 0 and NULL are different.
If I had to explain these concepts to somebody, I would draw two boxes.
The difference between 0 and null is like the difference between having a bank account with a 0 balance and not having a bank account at all.
This is best explained with pointers.
var a -> [0] // variable a is initialized and set to hold 0
var b -> [123asgeb0] // variable b is initialized but not set, memory is pointed to, but holds whatever was there last
Because of this, if your language does not keep track of NULL variables for you, then you will use whatever was already there. This can lead to overflows, stack corruptions, or other nasty side effects.
If the language does keep track of NULL variables, then it will have a stated case for handling this. I.E. treat NULL as zero, throw exception, treat as nop, etc.
To demonstrate for your boss, get a few pieces of used paper.
Judging by the variety of answers on this question, it can be concluded what we already know: null can mean many things.
Either it means the data is:
For this, and many other reasons, it is often useful to define a datastructure which encapsulates the meaning of the null value (e.g. an Optional class).
The person who -1ed this has no sense of humor. FWIW I am an Architect myself.
Null is emptiness. According to legend there are 17 flavors (possible meanings) of Null including "I dont know", "They didnt say", and "somebody broke this". Nobody knows why except DBAs and Architects who scowl from dark back rooms at mere programmers. They smile politely at bosses and wag their tongues in deep concern with impossibly complex wisdom that must be so, and is deeply important but not very clear - because bosses deliver important performance to the DBA and Architect. A pay check. Even so Nulls never tell their secrets. They are black holes of empty. One null never equals another null because each emptiness is empty of equality and refuses to admit that it might not be all the empty there is. This of course is conjecture because no self respecting null could ever actually admit it. Sometimes nulls suck vast quantities of real data into their (). This is called a bug but really is just the emptyness of the programmer's mind forgetting about nulls. So you see they do breed sometimes. But how or why is always quickly forgotten. () is sometimes a sign of a null or his cousin nil but paradoxically seldom a bug.
Zero is a definite quantity of something. Zeros are much happier than nulls. Zeros are merely lazy and dont add much to the outcome.
For the boring, very old accurate answer Null is a representation of "missing information and inapplicable information" E.F. Codd, creator of SQL.
Codd also invented 3rd Normal Form which is applied to the minds of young Computer Scientists as a form of preliminary torture. Once mastered it is completely disregarded . Similar things happen when Object Oriented Design is introduced. And we are never happy to leave it alone, so along comes impedance mismatch to add to the frolicking fray.
NULLis a state, not a value - it means you don't know the value. For SQL I normally use the party analogy to demonstrate.