Difference between 0 and null

[Former Member]

Hello everyone.

I have a requirement of distinguish zero values from null values. It seems the Visual Composer treats null values as zero values.

I have the following query in BI:

month..................ProcessA..................ProcessB................ProcessC

01.2009...........................10..............................................................5

02.2009.............................................................................................0

03.2009............................3...............................................................1

04.2009............................0...............................................................2

I'm trying to represent this in a line chart in visual composer. The axis has the month, and I defined 3 dataseries, one for each Process (A, B and C).

The issue here is that I don't have nothing in ProcessB, but the VC represents in the chart that dataserie has a line along the zero value. This is wrong. If don't have nothing in ProcessB, not even the value zero, this dataserie should not be represented (printed) in the chart.

Does anyone have any idea how to contourn this?

Thanks,

Diogo.

[talia_gurevich]

Hello Diogo.

When you define the ProcessB dataserie please put =false in the Formula field.

Regards,

Tali

[Former Member]

Hello Talia,

=false or simply false in the formula retrieves formula error.

Diogo.

[talia_gurevich]

Hi Diogo,

Try to put in the Formula field the following expression:

=IF(ISNULL(@FIELD),false,@FIELD)

where FIELD is as if the SeriesB

I hope this time it will help you.

Regards,

Tali

[Former Member]

Hi Talia,

I'm also working with that workaround although the system kind of behaves in a strange way sometimes...

Here's what I did:

Created another query with the accumulated values along the month but without displaying the month, like this:

QueryB:

ProcessA.........ProcessB...........ProcessC

13...............................................8

This query is like a pre-query. First the input is being sent to this pre-queryB that returns a table (not visible) and also the output of this pre-query does a *submit to the first (original) query.

In the original query I use a dataserie with the following formula:

NVAL(IF(ISNULL(#ID[ACC9LL]@ProcessB),'',@ProcessB_value))

The "#ID[ACC9LL]@ProcessB" is the reference (first executed) of the pre-query (queryB) that has the accumulated value, i.e., if along all the months if exists or not any value. If it doesn't exists, returns nothing (''), else it returns the actual value (@ProcessB_value). Since it is an IF, it returns a text, I have to convert it to numeric value therefore I used NVAL() function.

But this also doesn't refresh nicely with chart effects. I have to disabled the effects of the chart (interpolate, fold, slide) to work properly. Also, the scale of the Y axis is not being well adjusted (the auto-adjust option), does not work also fine if the first dataserie returned by the formula is the null value (''). So I also have to ensure that the first dataserie contains in fact anything (the returned value of the formula can't be null). If so (the first dataserie contains any value), this works fine.

Also you can't return false. False will be treated as 0, and thus will do the all dataserie be treated as zero along the X axis, and that is not true. With '', this will force to don't display any line for that dataserie.

Diogo.