0 Replies Latest reply: Jan 30, 2017 7:21 PM by I-Cheng Ma RSS

    Travel time aggregation

    I-Cheng Ma

      Hi,

       

      I am new to QlikSense and need help to calculate current travel time and historical travel time

      Attached is the data loading script.

       

      I have another chart which display current speed, historical speed and free flow speed.

      a variable is defined to get the max update time period index from DB. The speed aggregate every 15 minutes.

       

      TempDWHMaxUpdate:

      LOAD MaxUpdateTimestamp;

      SQL SELECT max(update_date) as MaxUpdateTimestamp FROM measure_update WHERE period = '15min' and start_date < current_date;

       

      if NoOfRows('TempDWHMaxUpdate') > 0 then

        vMaxTimePeriodIdxToday = peek('MaxUpdateTimestamp',0,TempDWHMaxUpdate) * 2 + Round(Minute(Timestamp(peek('MaxUpdateTimestamp',0,TempDWHMax))) / 30);

      ENDIF

       

      Drop Table TempDWHMaxUpdate;

      TRACE >>> Latest 15 min link update time period index:  $(vMaxTimePeriodIdxToday);

       

      The I use the following measure to get the

      Current speed: speed of current maximum time period

      Avg({<LinkStartDate={'$(=Date(Today(), 'YYYY/MM/DD'))'}, TimePeriodIdx={'$(=vMaxTimePeriodIdxToday)'}>}Speed)

       

      Historical Speed : same time period except today

      Avg({<LinkStartDate-={'$(=Date(Today(), 'YYYY/MM/DD'))'}, TimePeriodIdx={'$(=vMaxTimePeriodIdxToday)'}>}Speed)


      For Travel Time:

      Current Travel Time:

      Avg({<LinkStartDate={'$(=Date(Today(), 'YYYY/MM/DD'))'}, TimePeriodIdx={'$(=vMaxTimePeriodIdxToday)'}>}TravelTime) / 60


      Historical TravelTime:

      Avg({<LinkStartDate-={'$(=Date(Today(), 'YYYY/MM/DD'))'}, TimePeriodIdx={'$(=vMaxTimePeriodIdxToday)'}>}TravelTime) / 60


      These travel time is for individual link. How do I produce the continuous travel time along the route?

      TravelTime.png

      Appreciate any help

       

      Regards

      I-Cheng