Qlik Community

New to Qlik Community

Discussion board for questions on how to use Qlik Community and its features.

Announcements
This forum is for questions and information about how to use the Qlik Community.
Please do not post product related questions here.
Select the correct forum from: Qlik Product Forums
Highlighted
uzrgmppm
New Contributor

How to select records that are closest to specified Timestamp

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. 

1 Solution

Accepted Solutions
uacg0009
Valued Contributor

Re: How to select records that are closest to specified Timestamp

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

3 Replies
uacg0009
Valued Contributor

Re: How to select records that are closest to specified Timestamp

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
New Contributor

Re: How to select records that are closest to specified Timestamp

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
Valued Contributor

Re: How to select records that are closest to specified Timestamp

You're welcome, glad to help you~
Community Browser