Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

wizardo
Contributor III

null in fields used in set analisys

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

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: null in fields used in set analisys

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

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

4 Replies

Re: null in fields used in set analisys

May be try this:

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

balrajahlawat
Esteemed Contributor

Re: null in fields used in set analisys

MVP
MVP

Re: null in fields used in set analisys

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.

MVP
MVP

Re: null in fields used in set analisys

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

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

Community Browser