If Statement and Null Value Problems

[#1]

Hi guys, I have a small issue with an If statement and null values.

I have a range of values which are banded up based on another field.

The original field contains numbers between 0 and 15. The requirement is to include only numbers between 0 and 10 and exclude all numbers above 10.

Example

IF([Value 1] > 0 and [Value 1] < 5, '< 5',

     IF([Value 1] >= 5 and [Value 1] <10, >5'))  as New_Value.

I have no else part in my If statement.

When I use my new field called New_Value in a table, I try to calculate percentages and I find out that my percentages don't sum up to 100% when I use only <5 and >5, because there are some null values which correspond to values above 10 that I have excluded in my load script.

Is there a way I can get rid of all the unwanted values above 10, so that when i work out percentages in my table, my totals will sum up to 100% for only values between 0 and 10?

Thank you

[jonathandienst]

Hi

You can get the total using

Sum({<[Value 1] = {">=0 <=10"}>} [Value 1])

Then you can calculate the percentages only for values 0 - 10).

Hope that helps

Jonathan

[#3]

Hi Jonathan,

Thanks for your reply. Your suggestion would be fine if I had only one dimension, but I am using a cycle group in the table with multiple dimensions, so putting set analysis specific to one dimension would make the evaluation of other dimensions to be incorrect.

Any othr suggestions?

[erichshiino]

Hi,

You can create a variable ( for example vDim) with the following expression (in setting->Variable overview)

=GetCurrentField([New Group])   -----> Where [New Group] is the name of the group you are using

(including the equal sign, ok?)

Then, the set analysis will be:

Sum({<[$(vDim)] = {">=0 <=10"}>}[$(vDim)])

Hope this helps,

Erich