To display both date and time in Qlik Sense and calculate the difference between the Initiated Date Time and Connected Date Time, follow these steps:
1. Ensure Correct Timestamp Format: In your data load script, set the timestamp format to include both date and time:
SET TimestampFormat='DD/MM/YYYY hh:mm:ss';
2. Load Data with Correct Format: When loading your data, ensure the date-time fields are interpreted correctly:
LOAD
Timestamp([Initiated Date Time], 'DD/MM/YYYY hh:mm:ss') AS InitiatedDateTime,
Timestamp([Connected Date Time], 'DD/MM/YYYY hh:mm:ss') AS ConnectedDateTime
FROM [yourdata];
4. Calculate Time Difference: To calculate the difference between the two timestamps, use the Interval function:
Interval([ConnectedDateTime] - [InitiatedDateTime], 'hh:mm:ss') AS CallDuration
here’s an example of how your script might look:
SET TimestampFormat='DD/MM/YYYY hh:mm:ss';
LOAD
Timestamp([Initiated Date Time], 'DD/MM/YYYY hh:mm:ss') AS InitiatedDateTime,
Timestamp([Connected Date Time], 'DD/MM/YYYY hh:mm:ss') AS ConnectedDateTime,
Interval(Timestamp([Connected Date Time], 'DD/MM/YYYY hh:mm:ss') - Timestamp([Initiated Date Time], 'DD/MM/YYYY hh:mm:ss'), 'hh:mm:ss') AS CallDuration
FROM [YourDataSource];
I hope this helps.
