Formulae: Time differences?

dszl136154 · August 23, 2012 12:43PM

Hi - I attached an Excel File to give you an idea what I would like to do in Omniscope. I have groups, events and time when the events happened. I am trying to calculate the time difference between First and Last event amongst different groups (e.g. A,B) and have result always in the row with First Event. Could you help me with this problem? Thank you

paola · August 24, 2012 5:30AM

Merge the [Time] field with a Date. If you don't have date in your data set, just add the same date to Time, as that will enable Omniscope to treat this value as a point in time.
Then use SUBSET_RANGE([Time],SUBSET[Group])

dszl136154 · August 24, 2012 6:56AM

I have Date and Time together. Applied the above formula and got this error:

Error evaluating result for record 1: Argument 1 (stat_field) of function SUBSET_RANGE, value "Time": Unsupported type for Range of values (maximum minus minimum). Supported types are: Integer or Decimal Usage: SUBSET_RANGE(stat_field, subset_dataset) Calculates the statistical function [Range of values (maximum minus minimum)] of a field.

paola · August 24, 2012 8:28AM

Adding the date to time field (currently text):

TEXTTODATE([Time]+" " +"23/08/2012", "HH:mm:ss dd/MM/yyyy")

Then calculating the duration between start/end time (or first and last event time):

DATEDIFF(
SUBSET_MIN([Time Date], SUBSET([Group])),

SUBSET_MAX([Time Date], SUBSET([Group])),

"minutes")

Welcome!

Categories

Ideas Parade

Tagged