Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
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 (1)
1 Solution

Accepted Solutions
JGMDataAnalysis
Creator III
Creator III

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 III
Creator III

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!