Defined Missing Values
0
1
Edited Aug 31, 2015
If you have missing values defined in your data file, Report Builder will display a message. (See the message outlined in red in figure 1.0):
figure 1.0
If you do nothing, Report Builder will ignore the fact that the value(s) are defined as missing.
Create a Mean: Ignoring missing values:
Click the right arrow to create a measure for Mean. Notice that the syntax is simply:
avg(tp3)figure 1.1
Create a Mean: Using valid values only:
Click the "valid" toggle button. Notice the red text at the top that explains the missing values that are defined. Also notice that the missing items in the list are shown in red italic.
figure 1.2
Now when you click the right arrow to create a measure for Mean, the syntax takes into account the missing values.
The syntax is now:
avg(nullifeq(tp3,99))figure 1.3
This works for other measures too, not just Mean. You can verify by clicking on your newly created measures and looking at the syntax which is shown in the bottom-left corner of the
Edit Measures window. You'll
see the syntax
nullifeq(var, value1, value2, ...), which tells Report Builder to treat the var as missing when it equals any of the specified values.
Tip: In the example above, I might use "fill gaps" to populate the values -5 to +5. If "valid" is off, I'll need to select -5 and +5 before clicking
fill gaps, otherwise it will fill all the way from -5 to +99. But if "valid" is on,
fill gaps will ignore 99 and fill only the gaps between -5 and +5.
Comments