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

# Travel time aggregation

Hi,

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

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:

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

Historical Speed : same time period except today

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