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

    Travel time aggregation

    I-Cheng Ma



      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.



      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);



      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?


      Appreciate any help