10 Replies Latest reply: Dec 8, 2013 3:42 PM by Anderson Rodrigo Barretto Teodoro RSS

    Filling in data gaps with zero values



      Lets say I have this data for a month:


      Header 1Header 2


      I need to fill in the gaps between so that all the dates in the month are shown, but with zero against the ones that are zero. This is because when I try to plot this data on a chart I only get 5 values, despite me having created a master calendar so that the other dates exist. I need the chart to show the continous days with zero values against the days that are zero.


      Anyone help?

        • Re: Filling in data gaps with zero values

          I often have problems with this and am generally relieved when I don't as gaps in a timeseries are a massive no-no. Things you may want to try include:

          1. on the date dimension, tick the 'Show all values' checkbox

          2. on the presentation sheet, untick the 'Suppress zero values' and 'Suppress missing values'

          If that doesn't work, I have in the past resorted to setting up dummy values of 0 in my fact tables for each combination of date and the major dimensions my charts are shown by. I hated myself for doing so and haven't had to use it recently.


            • Re: Filling in data gaps with zero values

              Jon, thanks for your reply.


              1. The show all values checkbox kind of worked, but now the dimension axis doesn't adjust when data selections are made.


              2. No effect.


              How would I create dummy zero values in the fact tables? My data comes from a database so can't edit the source. My data structure is essentially this:


              Master Calendar:               Data:

              Timestamp                         Timestamp          Value

              01/04/11                             03/04/11               6

              02/04/11                             11/04/11               2







              Christian, I already have a master calendar. Not sure what you mean by sum(nothing)=0.



            • Re: Filling in data gaps with zero values
              Christian Conejero

              Create a calendar. they are many in these forums...

              In your table or chart, go to Properties > Presentation > Uncheck supress cero values and check show ceros.

              If your function is SUM, sum(nothing)=0, it'll show cero.

              • Re: Filling in data gaps with zero values
                Thomas Duvivier



                The problem is that if you have a selection, the missing dates are not associated, so will never be shown in the axis.


                I've solved once this problem by adding another expression like :




                And check the "Invisible" box in the Expressions tab.


                Hope it helps,



                • Re: Filling in data gaps with zero values
                  Anderson Rodrigo Barretto Teodoro

                  Hi Daniel


                  You need a reference table with all days, and then join with your desire table, like this:




                      REF_DATE as DATE





                  Inner Join(Reference_Table)


                      [Header 1] as DATE,

                      [Header 2] as hValues




                  After that:


                  change the missing values to "0", and then Supress Zero-Values and try this options:

                  • ALT( hValues, 0 ) as new_hValues  or..
                  • if(hValues='-',0,hValues) as new_hValues


                  Best Regards,