substract seconds from Max date and Min date

danimelo1 — Sat, 16 Nov 2024 02:10:52 GMT

Having this table

I'm expecting to have this result in a stright table:

I'm trying to use FirstSorted value and aggr functions with no success. My last formula is this:

=FirstSortedValue( second(Timestamp(Timestamp#(time, 'DD/MM/YYYY hh:mm:ss'))) , aggr(Max(time),conv_id) ) - FirstSortedValue( second(Timestamp(Timestamp#(time, 'DD/MM/YYYY hh:mm:ss'))) , aggr(Min(time),conv_id) )

What I'm missing. I know it could be easy via script editor, but I need to do this with set analysis. Any help?

Re: substract seconds from Max date and Min date

JGMDataAnalysis — Sat, 19 Oct 2019 19:10:35 GMT

Calculated Dimension:

=If(Aggr(NoDistinct Count(Distinct [Time]), [conv_id]) = 1, Second([Time]), Interval(Aggr(NoDistinct FirstSortedValue(Distinct [Time], -[Time]), [conv_id]) - Aggr(NoDistinct FirstSortedValue(Distinct [Time], [Time]), [conv_id]), 's') )

Re: substract seconds from Max date and Min date

danimelo1 — Sun, 20 Oct 2019 18:18:40 GMT

It worked, but as an expression in a straight table. I'm working with QlikView 12. Thanks for reply!

_id	conv_id	Time
0a2fcc4a-11e2-4c32-8c33-12f48c4f811d1570004556471	0a2fcc4a-11e2-4c32-8c33-12f48c4f811d	02/10/2019 8:22:36
0a03d094-9fe5-4b46-ae34-d7badcda4f2b1570025101516	0a03d094-9fe5-4b46-ae34-d7badcda4f2b	02/10/2019 14:05:02
0a03d094-9fe5-4b46-ae34-d7badcda4f2b1570025121945	0a03d094-9fe5-4b46-ae34-d7badcda4f2b	02/10/2019 14:05:22
0a4a04f8-dbe2-4c66-8976-4ae0ad857b6c1570001989478	0a4a04f8-dbe2-4c66-8976-4ae0ad857b6c	02/10/2019 7:39:50
0a4d57cc-c509-4ed6-a65d-879c13039e281570007045784	0a4d57cc-c509-4ed6-a65d-879c13039e28	02/10/2019 9:04:06
0a4d57cc-c509-4ed6-a65d-879c13039e281570007049487	0a4d57cc-c509-4ed6-a65d-879c13039e28	02/10/2019 9:04:10
0a4d57cc-c509-4ed6-a65d-879c13039e281570007058834	0a4d57cc-c509-4ed6-a65d-879c13039e28	02/10/2019 9:04:19
0a4d57cc-c509-4ed6-a65d-879c13039e281570007061502	0a4d57cc-c509-4ed6-a65d-879c13039e28	02/10/2019 9:04:22
0a4f61d5-5ec0-48bd-b6a5-f3d9df4df6f91570001096668	0a4f61d5-5ec0-48bd-b6a5-f3d9df4df6f9	02/10/2019 7:24:57
0a4f61d5-5ec0-48bd-b6a5-f3d9df4df6f91570001106809	0a4f61d5-5ec0-48bd-b6a5-f3d9df4df6f9	02/10/2019 7:25:07
0a5ddff5-42e0-465c-984f-f84730c6c0601570005822144	0a5ddff5-42e0-465c-984f-f84730c6c060	02/10/2019 8:43:42
0a5ddff5-42e0-465c-984f-f84730c6c0601570005830719	0a5ddff5-42e0-465c-984f-f84730c6c060	02/10/2019 8:43:51
0a5ddff5-42e0-465c-984f-f84730c6c0601570005836740	0a5ddff5-42e0-465c-984f-f84730c6c060	02/10/2019 8:43:57
0a5ddff5-42e0-465c-984f-f84730c6c0601570005843261	0a5ddff5-42e0-465c-984f-f84730c6c060	02/10/2019 8:44:03

topic Re: substract seconds from Max date and Min date in QlikView

substract seconds from Max date and Min date

Re: substract seconds from Max date and Min date

Re: substract seconds from Max date and Min date