Using an Expression in a List Box/Text Box for selecting Nulls

[ZoeM]

Hello Community.

I have a bit of a dilemma in my Dashboard.

I need to create a way to select nulls in my dahsboard so that the user can view information pending (not yet completed).

Ordinarily, I would like to use a Text Box with an action to Select In Field all the nulls, and I am using the below formula:

=If(IsNull([purchase date]),' ',0)

I created a List Box to see whether I would be able to select the nulls based on the two criteria: Declared and Not declared and the List Box does show the two options but I am unable to select either.

=(If(Len([purchase date])<1,'Missing','Purchased'))

Any ideas on how I can select all the nulls within a dimension? Ideally, in the Straight Table I created an expression to identify all the nulls within one of the dimensions either as 1 for not null and 0 for nulls.

Thanks for your time and hope something gets to work!

[ogautier62]

Hi,

in your listbox [purchase date] : select all values

then in an other field, sales for example : select excluded values

==> so you'll have null values of date

regards

[sunny_talwar · Accepted solution]

Why don't you create a new field while loading the data

If(Len([purchase date])<1,'Missing','Purchased') as MissingFlag

and not select Missing and it should work for you...

[ZoeM]

Thanks Sunny for your answer, that will work. Is there any way of doing it without having to create a new field when loading the data? Because the data fields will change going forward as the data set increases which means that I or another administrator will have to remember to add a new line item...

[sunny_talwar]

May be load with NULLASVALUE to convert nulls into empty spaces

NULLASVALUE *;

SET NullValue = '';

LOAD Toy,

    Cost,

    [# Items],

    [Purchase Date]

FROM

[Null Selection test.xlsx]

(ooxml, embedded labels, table is Sheet1);

[ZoeM]

And that is exactly what I have been looking for!

thanks so much Sunny!