If there is no value, then set "0" instead of empty

[#1]

Hi !

I'ld like to make a line chart that represent my sales for the 12 month before a specific date (I want the user to select the date).

[SOLVED] First problem : I don't know how to tell to my graph "when you have no value consider it as 0".

I tried with some "if count ...", "if total ...", "aggr", ...

In my graph, for the months Jul/Aug, I should have no value for the product "Orange", but the lines doesn't go to the "0".

[SOLVED] Second problem : I don't know how to filter my first dimension (time axis) to say "display only 12 month from ... to ...".

I've created 2 variables : "varFrom" and "varTo" that are the bounds I want (based on the selection).
I've changed my first dimension to be

=if(CalDate >= $(varFrom) and CalDate <= $(varTo), date(CalDate, 'MMM YYYY'), null())

instead of just "CalDate", but it doesn't work...

Third problem : instead of displaying just the value corresponding to the month in the X-axis, I would like to display the sum of the 4 last months corresponding to the month in the X-axis.

I tried with set analysis to make that sum, without succes.

The solution I found is to create another time table:

Caldendar :
LOAD DISTINCT Date AS CalDate
RESIDENT Sales;

And use that table as dimension, and use "if" statement :

=sum({$<Year=,Month=,Date=>} if(Date>addmonths(CalDate, -4) and Date<=CalDate, Amount))

Is there a solution to use only 1 time table ?

[#2 · Accepted solution]

hello,

In your graph , go into the presentation tab and uncheck the suppress zero value check box.

That should work

Philippe

[#3]

Second problem : I don't know how to filter my first dimension (time axis) to say "display only 12 month from ... to ...".

This method works. The problem came from the usage of the variables.

My variable have to contain the "=" sign

=makedate(max(Year), max(Month))

and my formula

=if(CalDate >= varFrom and CalDate <= varTo, date(CalDate, 'MMM YYYY'), null())

without the "$(" around the variable name.

IMPORTANT : don't forget to check the "suppress if null" in the dimension !

I don't know why "using a variable" works whereas using the same formula directly in the "if" statement don't...

[#4]

Philippe wrote:In your graph , go into the presentation tab and uncheck the suppress zero value check box.

Yes, it works !

Thank you !

(I was searching for that kind of checkbox elsewhere)

[johnw]

nmartin wrote: Third problem : instead of displaying just the value corresponding to the month in the X-axis, I would like to display the sum of the 4 last months corresponding to the month in the X-axis.
I tried with set analysis to make that sum, without succes.
The solution I found is to create another time table:
 Caldendar :
LOAD DISTINCT Date AS CalDate
RESIDENT Sales;
And use that table as dimension, and use "if" statement :
=sum({tiny_mce_markerlt;Year=,Month=,Date=>} if(Date>addmonths(CalDate, -4) and Date<=CalDate, Amount))
Is there a solution to use only 1 time table ?

Another solution is to create a linkage table to tie a month into the data for the past four months. See the third chart in the attached file.

Set analysis can't solve the problem because the set is only established once for the whole chart, not once per row (month) in the chart.

[#6]

Is it a better approach, or another approach ?

I will have to do this kind of table often, so I would like to do the best right from the start 🙂

The problem with the "DateLink" solution is that even if I uncheck "suprress null values", they are not displayed 😞

[johnw]

Not better or worse, just different and worth considering. Both approaches have their pros and cons. This probably isn't a complete list, but here's what I could think of quickly:

Link Table Pros

• executes quickly, even on large data sets
• I tend to think of data model solutions as working with QlikView, and complicated chart expressions as working against QlikView, but this may be my own bias

Link Table Cons

• takes extra time in the load (you could probably do a join rather than a loop to speed it up, but it'd still take time)
• does not allow you to show zeroes when you have no data, which is often a requirement, such as for trends over time
• requires version 8.5 and above for set analysis for good performance on some kinds of tables
• harder to understand
• cannot be extended to, for instance, the last 48 hours of timestamps for each timestamp
• extra memory required for extra table

Unlinked Field Pros (e.g., date islands)

• simple to create and understand
• allows you to show zeroes when you have no data, which is often a requirement, such as for trends over time

Unlinked Field Cons

• executes slowly on larger data sets
• selecting the unlinked field won't affect other charts
• selected the real, linked field will affect the chart using the unlinked field in "strange" ways

[#8]

Great answer. Thank you !

[johnw]

And in the interest of presenting all options, you can of course accumulate data in the script rather than in the chart. I've never done this, and would recommend against it unless you have NO other acceptable option, but this could just be personal bias. So here are the pros and cons I could come up with quickly:

Script Accumulation Pros

• executes very quickly, even on large data sets
• allows you to show zeroes when you have no data, which is often a requirement, such as for trends over time (need to explicitly load the zeroes, though)

Scrip Accumulation Cons

• takes extra time in the load
• can be complicated
• extra memory required for extra table
• data is specific to one chart and difficult to reuse