Calculating durations from change of state records?

[robinrouleur]

Can anyone suggest a way to do the following?

My source data is a set of event records containing change of state information for a group of objects, ie:

Timestamp, ObjectID, NewState

Where NewState is one of a set of possible object states, eg: A,B,C or D

What I'd like to be able to do is calculate the total time spent by each ObjectID in each state within the currently selected timestamp window, ie produce a table with rows of the form:

ObjectID, DurationA, DurationB, DurationC, DurationD

Any help would be greatly appreciated.

Robin

[Anonymous]

Hi Robin,

please can you post sample data.

[pokassov]

Hi!

t1:

load

     Timestamp,

     ObjectID,

     NewState,

     if(previous(ObjectID)=ObjectID, Timestamp-previous(Timestamp), now()-Timestamp)     as Duration

Resident your_table

Order By

ObjectID,

Timestamp desc;

drop table your_table;

[johanlindell]

Here's an example.

[Anonymous]

Hi,

Try:

Script:

Table:

Load  ObjectID,

           Timestamp,   

        NewState,

         if(ObjectID = Previous(ObjectID), Timestamp-Peek(Timestamp), Today()-Timestamp)     as Duration

Resident Data

Order By ObjectID,Timestamp desc;

Drop Table Data;

UI:

Dimension:

Select ObjectID as dimension.

Expression:

sum(aggr(sum({<NewState = {'A'}>}Duration) ObjectID))

sum(aggr(sum({<NewState = {'B'}>}Duration) ObjectID))

sum(aggr(sum({<NewState = {'C'}>}Duration) ObjectID))

sum(aggr(sum({<NewState = {'D'}>}Duration) ObjectID))

Regards

Neetha