2 Replies Latest reply on Feb 18, 2016 1:12 PM by DSKatSCL

    Fill in a chart with a zero (0) value if a record does not exist for every date

    DSKatSCL

      Title

      Fill in a chart with a zero (0) value if a record does not exist for every date

      Post

           I have a table that has a date of an event, type of event (up to 100+ different types) , and summary count of each event type. On a chart I need to have every day since the first of the year shown across the X axis, and the count of events on the Y axis. This is not a big issue, however, I DON'T have a record for each date for each type of event and I need to chart a zero value for each event type if there is no corresponding record for that event type.

           Without creating a script to add records for each date and event type that would have a zero value, is there a way t fill in the dates on a chart so that it would show every date (YTD) and a zero value if there is no related record with a count?  So I need to show Jan 1, 2014 - July 14, 2014 on each chart.  See the result I currently have in the attached screen shot.

           Thanks!

      Zero_for_every_Date.png

        • 1. Re: Fill in a chart with a zero (0) value if a record does not exist for every date
          philmodjunk

               I am assuming that you are charting only one event at a time.

               Only thing that I can think of is a variation of what you didn't want to do:

               Use a script to set up a related table with one record for each date. Set up a global field to use to match to your current table by event type. Define a relationship matching records by date and global event type field.

               Then a calculation field can compute the count of the related records to provide a count for each day and will provide a 0 count when there are no records of that event type for that date. You'd select event types from a value list on your global field to chart different events in your chart.

          • 2. Re: Fill in a chart with a zero (0) value if a record does not exist for every date
            DSKatSCL

            It been a while since I visited this. Thanks Phil for the input.  You confirmed what I suspected.

             

            Each chart displays one measure, and there may be new measures in the data that need to have a chart displayed from the first of the year.. I ended up doing was adding a section into my data import script to look at the current date and for each of the measures being imported it either:

             

            - for new measures, creates a record for each date from the beginning of the year to the current date

            - for existing measures, it updates the data to add new dates from the last record for the specific measure to the current date.

             

            Adds a little time to the import, but gives me what I need.