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