Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Time Wizard Charts using Start and End Timestamps

Hi

I've seen the various posts regarding intervals which seem quite complicated.

ust wondering if there is a calculation that can be used in Time Wizard Chart Example to identify concurrency.

Simply my data includes.

Name

Start TimeStamp - (Date and Time)

End TimeStamp - (Date and Time)

Duration

etc...

Example Data

Name,StartTime,EndTime,Status,Duration (sec)

L1 ABC,2017-05-01 06:01:49,2017-05-01 07:09:59,COMPLETE,4090

L1 ABC,2017-05-01 06:11:33,2017-05-01 07:25:22,COMPLETE,4459

L1 ABC,2017-05-01 07:01:56,2017-05-01 07:09:59,COMPLETE,489

L1 ABC,2017-05-01 07:21:23,2017-05-01 07:25:22,COMPLETE,299

1) I want a time chart using time to simply show how many processes running over the time occur concurrently. Example for a day.

To show what maxmimum concurrent process levels are.

2) Also want to another chart to do this using multiple dates which would follow each other.

Can this be done without extra tables being derived?

Regards,

Paul

2 Replies
marcus_sommer

I don't want to say that it's not possible but you will need some efforts and you will get some disadvantages, too. The reason for this is StartTime and EndTime won't have a common calendar - you will need to deceide to one or to try to build a common time-dimension from both calendars and/or using another one, possibly an unconnected within a loosen table. To them come quite heavy set analysis expressions to respect the selections within those calendars.

Personally I wouldn't try it. I think the pain with this will be much greater as creating intervals and quite probably I would use two intervals one for dates and one for times.

- Marcus

bnichol
Specialist
Specialist

Hi Paul,

I wasn't able to find a function to do this, so we created another table with date and time fields to allow congestion analysis.

Here's the script we used, where end timestamp is called ResultTimeStamp ...

Result:
load ID,
time(floor(1440*frac(StartTimeStamp + ((IterNo() -1)*(1/1440))+ (1/86400)))/1440,'hh:mm TT') as ClockTime,
Date(floor(StartTimeStamp + ((IterNo() -1)*(1/1440)))) as CalDate
Resident DistributionResults
while StartTimeStamp + ((IterNo() -1)*(1/1440)) + (1/86400) <= ResultTimeStamp + (1/11520)
order by ID, StartTimeStamp;

Here's a chart that displays the data, by one or multiple days...

And the data structure...

Please let me know if you find a simplier way.

Regards,

B