[Question] How to ignore the null value

other Xcelsius - Crystal Dashboard Design
1

Hi all,

Using xcelsius, I try to show some dynamic data by spreadsheet .
In the propetry page,I select the option of ignore null value, but it can only ignore the null value in the end of the data, how can I ignore the null value rows at the begin of the data?
Any suggestions!

taimoslong :malaysia: (BOB member since 2009-06-12)

2

Hi Taimoslong,
I would suggest you to use a filter that will send to a direction only the “not null” values.

You could write a formula in a column close that one with values and nulls that says something like: =if(isnull(cellX),0,1) <-- I don’t remember if is isnull or isblank :wink:
Now instead of an header with text for this column you will hardcode 1.
you will then use a combobox that will have as a label the entire the columns with the above formula and also the header (that’s needed to set the proper the default value).
You will use the combobox with “filtered rows” option of course. the destination of this selection will be your clean data set, with no null at the begin, neither at the end!

Hope this helps,

Luca Spinelli :it: (BOB member since 2008-08-27)

3

Cool tip to get rid of rows with no values in your data set :+1:

Andreas :de: (BOB member since 2002-06-20)

4

eheheh Thanks Andreas :wink:

Luca Spinelli :it: (BOB member since 2008-08-27)

5

Thanks luca,
That works for me,but I have another question,by doing that,the combobox have two options 1 and 0,when selecting 1,it shows the clean data what I want, when selecting 0 it shows null values. My question is that how can I make the selected option always be 1.

PS. Because my source columns are dynamic, with different options it may be null or not null. When the first column hava data,the combobox becames 1,the direction area shows the right data, and when the first column becames null,the combox changes to 0, then I got no data in the direction area.

taimoslong :malaysia: (BOB member since 2009-06-12)

6

Hi,
if you set the header as 1 hardcoded and you include that 1 into your labels and source data the combobox will automatically pick up the 1 values! :wink:

Hope this helps,

Luca Spinelli :it: (BOB member since 2008-08-27)

7

Thanks again luca, it works~ You are the god!

taimoslong :malaysia: (BOB member since 2009-06-12)