Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Having this table
_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 |
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?
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')
)
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')
)
It worked, but as an expression in a straight table. I'm working with QlikView 12. Thanks for reply!