Show 0 for x-axis when there is no data

[#1]

Hi,

I have a huge list of data with a number of X

I have made the below graph to count the number of times X appears into Y.

Sometimes the count is 0, the graph will skip the number X with 0 count.

Is there anyway the show the all the value of the X axis between my highest and lowest X with count.

[flipside]

Try de-selecting "Suppress Zero-values" on presentation tab.

flipside

[#3]

Tried.

It does not work as there is no value at all.

[flipside · Accepted solution]

Hi,

Okay, I understand your problem. I agree there should be a setting to allow this - continuous axis doesn't work as you need it - but as there isn't possibly the easiest way is to add the missing data to your tables in the load script. If this isn't possible, the following MIGHT work if the value is held in a table and not part of an aggregated value outside of the load script.

Use this as your dimension ...

=ValueLoop(min(total ID),max(total ID))

This will create a range from the smallest to largest value.  You then need to create 4 expressions (although you could possible merge into one once full tested).

1. =FieldIndex('ID',ValueLoop(min(total ID),max(total ID))) - label as Index

2. =concat({1} NumValue,';',ID) - label as AllValues, where NumValue is the value to plot

3. =subfield(AllValues,';',Index) - label as IDValue

4. =If(IDValue>0,IDValue,0) - final value to plot

You could then try to merge the expressions, or set the first three to invisible and max Axes to Max(NumValue).

flipside

EDIT:

The above is probably too complicated - you can do this instead in a single expression ...

COUNT:

=sum(IF(ID=ValueLoop(min(total ID),max(total ID)),1,0))

SUM:

=SUM(IF(ID=ValueLoop(min(total ID),max(total ID)),NumValue,0))