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

    Filling in data gaps with zero values

      Hi,

       

      Lets say I have this data for a month:

       

      Header 1Header 2
      01/04/116
      10/04/112
      11/04/113
      15/04/115
      21/04/111

       

      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
          Jonathan Brough

          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.

          Jonathan

            • 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

              03/04/11

              04/04/11

               

              etc

               

               

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

               

              Thanks

            • 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

                Hi,

                 

                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 :

                 

                Sum({1<Date={">=$(=min(Date))<=$(=max(Date))"}>}1)

                 

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

                 

                Hope it helps,

                 

                Thomas

                • 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:

                   

                  Reference_Table:

                  LOAD

                      REF_DATE as DATE

                  FROM

                      <your_reference_file.xls>;

                   

                  Your_Table:

                  Inner Join(Reference_Table)

                  LOAD

                      [Header 1] as DATE,

                      [Header 2] as hValues

                  FROM

                      <your_file.xls>;

                   

                  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,

                   

                  Anderson