1 of 1 people found this helpful
Create a calculated field similar to:
Place this on the filter shelf and select False.
Then, place [REGION.STATE] on the view.
Hope this helps!
Thank you so much for your quick response. But I do not want to add this calculated field in filter shelf. I just want a calculated field with out NULL value.
I am not sure if a calculated field could to created to exclude NULL. From what I understand when you create a calculated field, it is at a record level, i.e., for each record in your data set, the calculated field will be created. So even for the record which has Region as NULL, the calculated field would be created with NULL value or any other value you would want to substitute the NULL with. This is as I understand it, I might be wrong.
If you are trying to get a list of Regions without NULL and use as a filter, you could try creating a parameter out of the Region dimension (this would be created without the NULL) and use that Parameter to filter the data. This would be okay if the list of values in the Region column does not change often.
Thanks and Regards,
Hi Philip, what is the calculation you want to do? You might be able to use the IFNULL function which will allow you to deal with the nulls.
In general how to create a calculated field based on WHERE Condition. I want to calculate All those state WHERE STATE IS NOT NULL
In general how to create a calculated field based on WHERE condition? Here what I am doing, I am calculating state WHERE state IS NOT NULL
I have a similar dilemma:
Let's say we have a statement (in English):
On Time Calculate Field: If [Actual Start Date] <= ( [Scheduled Start Date] - 2) then enter a "1" in this spot unless it's null. If that's the case, leave it null.
which I thought would be written as:
IF ISNULL [Actual Start Date] OR
if ([Actual Start Date] -2) <= [Scheduled Start Date] then 1
The Excel statement is : =IF([Actual Start Date]="","",IF([Actual Start Date]-2<=[Scheduled Start Date],1,""))
Alas, it doesn't work. What am I doing wrong???
You've got a lot of options...here's a few
-Formatting the axis of a chart to hide null values >Right Click Axis>Format>Special Values>Hide
-Drop State on the filter card and unselect null values
-Create a calculation that uses IsNull to flag the record and filter nulls or symbolize differently
btw...since State is not numeric, you may need to use the Attr function with IsNull
Sorry about that Matt, I didn't realize your question was the current one...unless your [Actual Start Date] is a smartkey (ie. an integer like 20131107) your problem is likely with subtracting the 2. You'll need to tell it 2 years or 2 months or 2 days...
No probs, Mark.
I actually have the date as an integer…it’s appearing like I have logic issues in the statement. Basically, I need to see if the date is blank (null). If so, leave blank. If it’s within 2 days of the scheduled start date then write a 1 to the column…if not, make it null. I then add up the 1’s and see how many jobs are started ontime.
I can’t find any real useful resources on this kind of conditional statement – I’m totally baffled!!!!
maybe something like this...
iif([Actual Start Date]-[Scheduled Start Date]<=2,1,NULL)
Matthew, NULLs and 'blanks' are not the same thing. Post a package workbook with some sample data and we can help you work it out.
Thanks, Mark. This is exactly what I needed!!!
Gotcha...a little research shows null is a no value field (of any kind) whereas the blank is a string. I stand corrected!!