3 Replies Latest reply: Dec 3, 2012 8:58 AM by Dave Riley RSS

    Show 0 for x-axis when there is no data

    Hui Ming Wang

      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.

      qv_fill_in_the_blank.JPG

        • Re: Show 0 for x-axis when there is no data
          Dave Riley

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

           

           

          flipside

            • Re: Show 0 for x-axis when there is no data
              Hui Ming Wang

              Tried.

               

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

                • Re: Show 0 for x-axis when there is no data
                  Dave Riley

                  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))