February 13, 2017 at 1:07 pm
Hey friends!
I'm having a heck of a time here.
I have an expression that should be returning 0 rows but instead it is returning a different number. The query has 19 rows returned and each of them has Null for the Income. I need to be able to count distinct clients when the income = 0 and by my reasoning, this should work because it works fine when comparing it to other ranges like > 0 and <= 150.
=CountDistinct(IIF(Fields!Income.Value = 0, Fields!ClientID.Value, Nothing))
The kicker is that I have another row where I'm inspecting if it was never filled out, and that is where I'm looking for the income to be Is Nothing.
=CountDistinct(IIF(Fields!Income.Value is nothing, Fields!ClientID.Value, Nothing))
So, why is the Income.Value = 0 returning all 19 rows just the same as the Income.Value Is Nothing when it is clear that they are all Null and definitely not 0?
What am I missing here?
Thanks!
February 13, 2017 at 3:38 pm
Edit: Misunderstood, sorry. Can your incomes have a value lower than 0? For example, couldyou instead do:=CountDistinct(IIf(IIf(IsNothing(Fields!Income.Value), -1, Fields!Income.Value) = 0, Fields!ClientID.Value, Nothing))
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 14, 2017 at 9:17 am
The income would never fall below 0. I also found that adding IsNumeric() to the expression also does the trick.
Thank you for your help! I appreciate it.
Jeff
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply