Travel time aggregation

[#1]

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?

Appreciate any help

Regards

I-Cheng