5 Replies Latest reply on Jul 5, 2018 9:41 AM by GreggSmith

    Component to show Created Time Trend

    TxtEva Apprentice

      This might be a really easy question (we've only had Xtraction 2017.2 for a couple of days so still finding out about all the buttons).

       

      I'm trying to create an average time of day created chart linked in to HEAT/Ivanti Server Manager. Something like this (from an old system):

      I've managed to get it for one day:

      with these settings:

       

      But when I change the Created Date to more than one day then it doesn't work - one day is spread hourly and the other day(s) are all logged at 00 hours.

      I've tried changing the Summary from Count to Average but then it wants a Summary field and none of those options seem to be set to Created date or Time logged.

       

      Any advice would be much appreciated, thank you!

        • 1. Re: Component to show Created Time Trend
          Waldy Employee

          I think the problem is that you are trying to use a time component for this.  A time component only gets split into hours when you show a single day.

           

          You need a group component displaying columns, but looking at the standard ISM data model, I don't see Time Logged, have you created this?  That is what you need, a group field that returns the hour of the day the ticket was logged.

           

          The field expression for Incident:

           

          RIGHT('0' + CONVERT(VARCHAR(2),DATEPART(HOUR,DATEADD(HOUR,DATEDIFF(HOUR,GETUTCDATE(),GETDATE()),INCIDENT.CREATEDDATETIME))),2) + '-'

          + RIGHT('0' + CONVERT(VARCHAR(2),DATEPART(HOUR,DATEADD(HOUR, 1, DATEADD(HOUR,DATEDIFF(HOUR,GETUTCDATE(),GETDATE()),INCIDENT.CREATEDDATETIME)))),2) + 'hrs'

           

          I've included leading zeros as you may have sorting problems otherwise.

           

           

          1 of 1 people found this helpful
          • 2. Re: Component to show Created Time Trend
            TxtEva Apprentice

            That worked first time - easy when you know how! Thank you so much!

             

            TimeLogged.png

            • 3. Re: Component to show Created Time Trend
              GreggSmith SupportEmployee

              Waldy's solution is the most generic one I would propose.  We've also mapped this a few times in some connectors, but not extensively.

               

              Please be aware that this is not always 100% correct; however it is the closest you are going to get for a "simple, generic fix".

               

              What the code does is return the hour portion of a date time field that is stored in UTC adjusted to the current time zone of the database server.

               

              So, here are the limitations of that approach that you have to live with (or create a more-complex, less-generic solution):

              1. The results are based on the current timezone offset of the database server.  That may be fine unless the database server is not in your timezone.
              2. The results are based on the current timezone offset of the database server.  That may be fine unless multiple different people want reports based on multiple different timezones.
              3. The results are based on the current timezone offset of the database server.  If your timezone observes daylight savings, then reporting on dates in the current offset window will be correct and dates in the other offset window will be an hour off (either an hour behind or an hour ahead - or 30 minutes if you are in a timezone that only adjusts 30 minutes).

               

              As you can see, all of the limitations are a result of the same design decision.

              1 of 1 people found this helpful
              • 4. Re: Component to show Created Time Trend
                TxtEva Apprentice

                That's useful to know, thank you for the heads up.

                 

                In our case the server would be in the same timezone as all of our supported users (luckily) but I'll bare in mind the Daylight Savings time changes.

                • 5. Re: Component to show Created Time Trend
                  GreggSmith SupportEmployee

                  If your dashboards/reports that use this field are only reporting on the current/previous day, week, or month, then you will only have an issue for 1 or 2 days, weeks, or months right after a daylight savings change.  During that time, some or all of the dates may be off depending on whether daylight savings hit in the middle of or after the date range.

                   

                  If you are reporting on last week/month and DS hit this week/month, that means that ALL of last week/month's hours are off.

                  If you are reporting on last/this week/month and DS hit in the middle of that range, that means that the records prior to the DS hit are an hour off and the records since the DS hit are correct.  The incorrect values will be either an hour ahead or an hour behind depending on whether you are going on or off DST.

                  If you are reporting on the last 12 months, pretty much 1/2 your data will always be off.  But WHICH 1/2 will vary based on whether you are currently on or off DST.