Nulls in charts

[#1]

I'm having problems with displaying nulls in charts. I'm either seeing them correctly and getting extra nulls I don't want, or I'm not seeing nulls at all.

My data is a series of file sizes by days.

My data has nulls at the beginning (no files), then the data starts (file sizes), and there are occasional nulls throughout the data (days when no files were written). I want to ignore the 'leading' nulls and only plot the data from the first non-null data. BUT I want all subsequent nulls to be shown on my chart.

When I plot out the data showing nulls, all my nulls are shown, including the leading nulls. When I turn off the nulls, it skips my nulls in the body of my data.

I can't find an easy way to skip the leading nulls and just show the nulls in the middle of my data.

Has anyone got any ideas?

(One thing I left out here: I have multiple data files that start at different dates. They are all joined on the date field. My null charts always start from the earliest date across all files.)

[sunny_talwar]

It would be very difficult to pinpoint the issue without looking at your application. Would you be able to share a reduced version of your application?

Best,

Sunny

[#3]

Here's a version of my underlying data (the real data is spread across several files.

I've attached an app that shows the problem.

Thank you!

[sunny_talwar]

I did not realize that this was a Qlik Sense question. I don't have Qlik Sense installed on my computer. I hope some one else would be able to give you assistance.

Best,

Sunny

[ramoncova06]

what do you mean by leading nulls, for Qlikview is either a null or not if you believe they have space in them, then you could try with a len

[#6]

By leading nulls I mean nulls before the first data item in the series.

I'm thinking of doing something with set analysis, but this seems like overkill for what should be quite straightforward.

[rubenmarin]

Hi Mike, I checked your app and those aren't null values, those are values wich u=0, so there is a value but it's equal to zero.

You can try to use a dynamic dimension to pick the dates that are posterior to the first date sith Sum(u)>0:

Aggr(If([FileDate]>=Min(TOTAL {<[FileDate]={"=Sum(u)>0"}>} [FileDate]), [FileDate]), [FileDate])

[#8]

Hi Ruben,

The first problem is aggregation. Although my data is null, it seems like sum(u) returns 0 if the sum of u values is null. This kind of feels like an error. I don't think it's the QlikView default behavior?

In any case, I'm plotting just u now. This means my leading values are definitely null. I still get the same problem.

You can see my trailing values are null in the attached (updated) example.

By the way, the table shows an inconsistency. In a table, the sum of values including a null is a null. In a chart, the sum of values including a null is not null!

Mike

[rubenmarin · Accepted solution]

Hi Mike, those values aren't zero but also aren't null, they are an empty string, but doesn't matter because, as you said sum(null())=0 and also Sum('')=0, but in any case, supressing null values wouldn't have any effect.

So allow me to insist in the previous solution, to filter dates to show only those after the first sum(u)<>0 is encountered, and this should be sum() or another aggregation function because if there are more than one value for the same date, it will return null() unless you use an aggregation function.

Please check attachment.

Another option can be check for empty strings in script, and load null() instead of the empty string, something like:

LOAD If(u='', null(), u) as u

.....

from ...

Hope this helps!