topic Re: help calculating differnce in days, based off multiple factors in QlikView

help calculating differnce in days, based off multiple factors

drohm002 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: help calculating differnce in days, based off multiple factors

vishsaggi — Thu, 07 Jun 2018 20:39:44 GMT

Did you try like LastDate - FirstDate?

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

Re: help calculating differnce in days, based off multiple factors

settu_periasamy — Thu, 07 Jun 2018 20:52:39 GMT

May be try this.

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

Re: help calculating differnce in days, based off multiple factors

vishsaggi — Thu, 07 Jun 2018 20:58:28 GMT

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

Re: help calculating differnce in days, based off multiple factors

sergio0592 — Thu, 07 Jun 2018 20:59:06 GMT

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

Is it what you're trying to achieve?

Re: help calculating differnce in days, based off multiple factors

Anonymous — Fri, 08 Jun 2018 09:58:51 GMT

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;

Re: help calculating differnce in days, based off multiple factors

drohm002 — Fri, 08 Jun 2018 12:19:21 GMT

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...

Re: help calculating differnce in days, based off multiple factors

drohm002 — Fri, 08 Jun 2018 12:24:09 GMT

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....

Re: help calculating differnce in days, based off multiple factors

drohm002 — Fri, 08 Jun 2018 12:26:45 GMT

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

Re: help calculating differnce in days, based off multiple factors

olivierrobin — Fri, 08 Jun 2018 12:37:37 GMT

hello

load your data

and in your graph, use :

ID as dimension

and

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

as expression