Multiple time lapses

[wanyunyang]

I'm not able to show company data so I made up a similar scenario: I'm trying to calculate the time lapse between Question Status and Answer Status. And the problem is, what should the expression be if there are multiple Question-Answer pairs, which leads to multiple time lapses?

For example, in below scenario, there should be two time lapses: 00:03 (from Id 336 and 339) and 00:04 (from Id 346 and 347).

Thank you in advance!

[Quy_Nguyen]

How do you know which answer belongs to which question?

[wanyunyang]

Based on Id is fine. Just get the closest Answer to each Question. .

[Quy_Nguyen]

OK. Assumed that every question has the answer, the idea is create a Question-Answer table from your data to achieve your request. Here is the sample code for you:

Temp:
LOAD
    RowNo() As PairID,
    ID as ID_Question,
    "Time" As TimeStart
FROM [YourConnection]
Where Status = 'Question';
Join
LOAD
    RowNo() As PairID,
    ID as ID_Answer,
    "Time" As TimeEnd
FROM [YourConnection]
Where Status ='Answer';

FinalData:
Load *, Time(TimeEnd-TimeStart,'hh:mm') As TimeLapse
Resident Temp;

Drop Table Temp;

[wanyunyang]

Hi Quy, thanks for your reply, but unfortunately, not every Question has Answer. What I'm thinking of is if there's a way to use above() function in chart.

[dwforest · Accepted solution]

You could use something like

If (Previous(Status)='Question' and Status='Answer', time-Previous(time), 0)

[wanyunyang]

Hi David, it says Previous is not a valid function.

[wanyunyang]

Oh I see. It's a script function. Thanks David!