Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
danimelo1
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? 

Labels (2)
1 Solution

Accepted Solutions
JGMDataAnalysis
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
JGMDataAnalysis
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

 

danimelo1
Creator
Creator
Author

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