Remove the blank values from a chart

[Anonymous]

Hi,

I have a chart that will display the products (Dimension) by the count of the IDs (Expression)

But I am getting the products with blank values.

How can I create a dashboard that will exclude the null blank values, I have tried the following:

- Dimension: ='=Product<>'' ''' - Tried with single and double quotes.

- Expression: =Count({< Product = {"<> "" "}>} AutoID) - Tried with single and double quotes

Both syntaxes are correct, but the dashboard doesn't display any data.

Any idea?

[sunny_talwar · Accepted solution]

Is this a different question then this one? How can filter by Null/Blank/Empty fields

May be try this:

Dimension

Product

Expression:

=Count(AutoID = {"=Len(Trim(Product)) > 0"} AutoID)

[jcs_2015]

Sounds like you should be ale to Suppress Nulls in the Dimension setting, hard to tell without an example though.

[Anonymous]

This one worked:

=Count({<Product= {"=Len(Trim(Product)) > 0"} >}AutoID)

[sunny_talwar]

The one I proposed did not work?

[Anonymous]

You both were right. But now, how can I perform a dashboard for those ones that were excluded?

[jcs_2015]

In my experience Qlik does not like Nulls.  Best practice in my opinion is to "create" a value for the Nulls in your load script, likely via a nested If statement, depending on how many values are in your Dimension and if there is a finite list of values.

[Anonymous]

Yes, it worked but I must modify it as you posted this:

=Count(AutoID = {"=Len(Trim(Product)) > 0"} AutoID)

The one that worked was this:

=Count({<Product= {"=Len(Trim(Product)) > 0"} >}AutoID)

Now, I need to create a dashboard with the records excluded, is it possible?

[jcs_2015]

Try something like this in your load script:

LOAD
If(xDimension = 'A','A',
If(xDimension = 'B','B',
If(xDimension = 'C','C',
If(xDimension = 'D','D',
'Null')))),
Value
FROM

(ooxml, embedded labels, table is Sheet1)

You'll get this, and a value to build your exception object(s) from

[Anonymous]

it almost worked. The problem is that for blank values QV uses '-', so when I did that all worked except for the blank ones.

I have tried something like this, but for the empty/blank fields QV still putting a '-'

If(fieldname = ' ', 'Test value', fieldname) As Testfield

If(fieldname = NULL, 'Test value', fieldname) As Testfield

If(fieldname = 'NULL', 'Test value', fieldname) As Testfield

none of the above worked.