null in fields used in set analisys

[wizardo]

i have a field "Status"

i want to sum all sales where Status = 'Approved'

i use this

sum( {<Status={'Approved'}>}  Sales).

this works as axpected.

my problem is when i have only null() values in the Field Status.

in that case i would have like the expression above to return 0 (zero) or at least null

instead it just give me the sum of all sales regardless of whats in the Status Field (EG just all the sales)

how can i tell it to work as i need?

thanks

Daniel

[sunny_talwar]

May be try this:

Sum({<$*Status={'Approved'}>}  Sales)

[Anonymous]

did you check this?

Value NULL in set analysis?

How to handle NULL values​​, ANDs and ORs in SET ANALYSIS: | QlikView Help, Tips & Hints

[swuehl]

I think the most easiest solution would be to add a dimensional table for Status field, e.g. via an INLINE LOAD in your script, so that you have at least one value to make selections in:

STATUS:

LOAD Status [

Status

Approved

];

Even if your fact table only shows NULL in field Status, the set analysis should now work as expected.

[swuehl · Accepted solution]

And if you don't want to change the data model, try maybe

=Sum({<Status= {'Approved'}>*<Sales = {"=Count(Status)"}>}  Sales)