help calculating differnce in days, based off multiple factors

[drohm002]

Please see the table below.  I would like to calculate the duration of each ID, the difference between the last date and the first date, in relation to event X.  So for ID 1, the answer I am looking for is four days, but I do not know the logic and calculation to get there....please help!

ID	event	time
1	x	6/5/2018
1	x	6/6/2018
1	x	6/7/2018
1	x	6/8/2018
2	x	6/9/2018
2	x	6/10/2018
2	x	6/11/2018
3	x	6/12/2018
3	x	6/13/2018
3	x	6/14/2018

[vishsaggi]

Did you try like LastDate - FirstDate?

IN your sample data there is no lastdate and first date fields.

[settu_periasamy]

May be try this.

=Max(TOTAL  <ID>time)-Min(TOTAL  <ID>time)+1

[vishsaggi]

Hush! I got it now. Hes is looking for Min and Max times by ID.

[sergio0592]

Hi, and if you just use max and min in a chart?

Is it what you're trying to achieve?

[Anonymous]

Daniel,

try the below snippet

Temp_cumulative:
LOAD ID,event, Date(Date#(time,'MM/DD/YYYY')) AS time;
LOAD * Inline [
ID event time
1 x 6/5/2018
1 x 6/6/2018
1 x 6/7/2018
1 x 6/8/2018
2 x 6/9/2018
2 x 6/10/2018
2 x 6/11/2018
3 x 6/12/2018
3 x 6/13/2018
3 x 6/14/2018
]
(delimiter is ' ');

SET VEvaluate= ($1-$2)+1;

Left Join (Temp_cumulative)
LOAD *,
$(VEvaluate(num(Maxtime),num(Mintime))) AS duration;
LOAD
ID, event,
Date(max(time)) AS Maxtime,
Date(min(time)) AS Mintime
Resident Temp_cumulative Group by ID,event;

[drohm002]

that is because there is only one column in my data that has the date.  I am looking for the duration of event 'X', for each ID.  So the duration for ID 1 is 4 days...

[drohm002]

I need to find the duration of event 'X' for each ID.  So the correct answer for ID 1 is a duration of 4 days for event X....

[drohm002]

all my dates are in the same column, I do not have two separate columns of min and max

[olivierrobin]

hello

load your data

and in your graph, use :

ID as dimension

and

=FirstSortedValue(time,-time)-FirstSortedValue(time,time)+1

as expression