2 Replies Latest reply: Jun 22, 2017 11:40 AM by Brent Nichol RSS

    Time Wizard Charts using Start and End Timestamps

    Paul Atkinson



      I've seen the various posts regarding intervals which seem quite complicated.

      ust wondering if there is a calculation that can be used in Time Wizard Chart Example to identify concurrency.


      Simply my data includes.


      Start TimeStamp - (Date and Time)

      End TimeStamp - (Date and Time)




      Example Data

      Name,StartTime,EndTime,Status,Duration (sec)

      L1 ABC,2017-05-01 06:01:49,2017-05-01 07:09:59,COMPLETE,4090

      L1 ABC,2017-05-01 06:11:33,2017-05-01 07:25:22,COMPLETE,4459

      L1 ABC,2017-05-01 07:01:56,2017-05-01 07:09:59,COMPLETE,489

      L1 ABC,2017-05-01 07:21:23,2017-05-01 07:25:22,COMPLETE,299


      1) I want a time chart using time to simply show how many processes running over the time occur concurrently. Example for a day.

      To show what maxmimum concurrent process levels are.


      2) Also want to another chart to do this using multiple dates which would follow each other.


      Can this be done without extra tables being derived?





        • Re: Time Wizard Charts using Start and End Timestamps
          Marcus Sommer

          I don't want to say that it's not possible but you will need some efforts and you will get some disadvantages, too. The reason for this is StartTime and EndTime won't have a common calendar - you will need to deceide to one or to try to build a common time-dimension from both calendars and/or using another one, possibly an unconnected within a loosen table. To them come quite heavy set analysis expressions to respect the selections within those calendars.


          Personally I wouldn't try it. I think the pain with this will be much greater as creating intervals and quite probably I would use two intervals one for dates and one for times.


          - Marcus

          • Re: Time Wizard Charts using Start and End Timestamps
            Brent Nichol

            Hi Paul,


            I wasn't able to find a function to do this, so we created another table with date and time fields to allow congestion analysis.


            Here's the script we used, where end timestamp is called ResultTimeStamp ...

            load ID,
            time(floor(1440*frac(StartTimeStamp + ((IterNo() -1)*(1/1440))+ (1/86400)))/1440,'hh:mm TT') as ClockTime,
            Date(floor(StartTimeStamp + ((IterNo() -1)*(1/1440)))) as CalDate
            Resident DistributionResults
            while StartTimeStamp + ((IterNo() -1)*(1/1440)) + (1/86400) <= ResultTimeStamp + (1/11520)
            order by ID, StartTimeStamp;


            Here's a chart that displays the data, by one or multiple days...


            And the data structure...

            Please let me know if you find a simplier way.