How to select records that are closest to specified Timestamp

[uzrgmppm]

Hi all,

I have a single table that contains camera statuses including historical records, for example:

       CameraID      Status         UtcTimestamp
1     3742                Failure        2019-01-02 04:41:19.000
2     1532                Failure        2019-01-02 04:30:19.000
3     3742                Unknown  2019-01-02 03:41:19.000
4     1437                Normal      2019-01-02 03:30:19.000

Notice, that Camera 3742 repeats twice, because its status was changed. My idea is to allow user to pick a timestamp on dashboard and all visualizations should show data that is less or equal to specified timestamp and closest to it. For example if user will specify '2019-01-02 05:00:00.000' all calculations should be made on this dataset:

       CameraID      Status         UtcTimestamp
1     3742                Failure        2019-01-02 04:41:19.000
2     1532                Failure        2019-01-02 04:30:19.000

4     1437                Normal      2019-01-02 03:30:19.000

Seems I figure out how to do such expression: 

Aggr(Max(if(UtcTimestamp<=Date(SelectedTimestamp), UtcTimestamp)), CameraID)

But then I stuck. I have a KPI that should show count of cameras that is offline (Status=Failure) and same KPI for online (Status<>Failure).  I tried this but it gives incorrect result:

Count(Aggr(Max(if(UtcTimestamp<=Date(SelectedTimestamp)  AND Status='Failure', UtcTimestamp)), CameraID))

For the SelectedTimestamp='2019-01-02 05:00:00.000' it should return 2 offline cameras and 1 online. I appreciate any help because I really despaired. 

[uacg0009 · Accepted solution]

Hi Uzrgmppm,

Because I'm not sure where you use this expression, then I assume that you use it in a text object. If not, you may need to tell me the table dimensions.

Please try to use below expression, it works in my desktop:

=count(if(UtcTimestamp = Timestamp(Aggr(NODISTINCT Max(if(UtcTimestamp<=Date(SelectedTimestamp), UtcTimestamp)), CameraID)) and Status = 'Failure',CameraID))

=count(if(UtcTimestamp = Timestamp(Aggr(NODISTINCT Max(if(UtcTimestamp<=Date(SelectedTimestamp), UtcTimestamp)), CameraID)) and Status <> 'Failure',CameraID))

Aiolos Zhao

[uzrgmppm]

Brilliant, it works as a want!!! I was so close to your solution but I can't get fully the idea behind Qlik syntax and what actually happens under the hood. Thanks a lot, uacg0009! 

[uacg0009]

You're welcome, glad to help you~