Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
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!