Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

substract seconds from Max date and Min date

Having this table

_idconv_idTime
0a2fcc4a-11e2-4c32-8c33-12f48c4f811d15700045564710a2fcc4a-11e2-4c32-8c33-12f48c4f811d02/10/2019 8:22:36
0a03d094-9fe5-4b46-ae34-d7badcda4f2b15700251015160a03d094-9fe5-4b46-ae34-d7badcda4f2b02/10/2019 14:05:02
0a03d094-9fe5-4b46-ae34-d7badcda4f2b15700251219450a03d094-9fe5-4b46-ae34-d7badcda4f2b02/10/2019 14:05:22
0a4a04f8-dbe2-4c66-8976-4ae0ad857b6c15700019894780a4a04f8-dbe2-4c66-8976-4ae0ad857b6c02/10/2019 7:39:50
0a4d57cc-c509-4ed6-a65d-879c13039e2815700070457840a4d57cc-c509-4ed6-a65d-879c13039e2802/10/2019 9:04:06
0a4d57cc-c509-4ed6-a65d-879c13039e2815700070494870a4d57cc-c509-4ed6-a65d-879c13039e2802/10/2019 9:04:10
0a4d57cc-c509-4ed6-a65d-879c13039e2815700070588340a4d57cc-c509-4ed6-a65d-879c13039e2802/10/2019 9:04:19
0a4d57cc-c509-4ed6-a65d-879c13039e2815700070615020a4d57cc-c509-4ed6-a65d-879c13039e2802/10/2019 9:04:22
0a4f61d5-5ec0-48bd-b6a5-f3d9df4df6f915700010966680a4f61d5-5ec0-48bd-b6a5-f3d9df4df6f902/10/2019 7:24:57
0a4f61d5-5ec0-48bd-b6a5-f3d9df4df6f915700011068090a4f61d5-5ec0-48bd-b6a5-f3d9df4df6f902/10/2019 7:25:07
0a5ddff5-42e0-465c-984f-f84730c6c06015700058221440a5ddff5-42e0-465c-984f-f84730c6c06002/10/2019 8:43:42
0a5ddff5-42e0-465c-984f-f84730c6c06015700058307190a5ddff5-42e0-465c-984f-f84730c6c06002/10/2019 8:43:51
0a5ddff5-42e0-465c-984f-f84730c6c06015700058367400a5ddff5-42e0-465c-984f-f84730c6c06002/10/2019 8:43:57
0a5ddff5-42e0-465c-984f-f84730c6c06015700058432610a5ddff5-42e0-465c-984f-f84730c6c06002/10/2019 8:44:03

 

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

ResulQlik.png

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? 

1 Solution

Accepted Solutions
Highlighted
Creator II
Creator II

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')
)

 QlikCommunityChallenge_Seconds.PNG

 

View solution in original post

2 Replies
Highlighted
Creator II
Creator II

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')
)

 QlikCommunityChallenge_Seconds.PNG

 

View solution in original post

Highlighted
Creator
Creator

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