topic Re: counting time difference between dates in the same column in QlikView

counting time difference between dates in the same column

Qliksense7 — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone,

I have an issue with how to proceed with an expression for counting the time difference in the column "Period" which is included in the format 'M/D/YYY hh:mm'. I need to find if the value mentioned is going below a limit continuously ( present as a different field) over a period of 10 minutes and count every occurrence as 1 . I've attached the sample data below.

Thanks for the help!

Elements	Period	value
a	6/1/2019 0:00	345
a	6/1/2019 0:01	456
a	6/1/2019 0:02	323
a	6/1/2019 0:03	322
a	6/1/2019 0:04	345
a	6/1/2019 0:05	432
a	6/1/2019 0:06	312
a	6/1/2019 0:07	345
a	6/1/2019 0:08	234
a	6/1/2019 0:09	334
a	6/1/2019 0:10	356

Re: counting time difference between dates in the same column

Vegar — Tue, 09 Jul 2019 05:45:19 GMT

You did not provide any information on the period field used for comparison so in my example below is called "ComparingPeriod".

Try this expression:

Count({< Period= {">$(=only(ComparingPeriod)- interval#(10,'mm'))<=$(=only(ComparingPeriod))"} >}Elements)

Re: counting time difference between dates in the same column

Qliksense7 — Tue, 09 Jul 2019 05:49:45 GMT

Hi,
So for say the low limit is 370 and in this set of values for 10 minutes interval if its goes all below 370 my count should produce a 1 or else its 0. So it has to scan through the 'period ' for every 10 minutes to check that if the values falls below the low limit set

Re: counting time difference between dates in the same column

Vegar — Wed, 10 Jul 2019 07:15:43 GMT

Is this what you are trying to do? Per 10 min interval count how many transactions that are in violation of your defined threshold interval?

Re: counting time difference between dates in the same column

Vegar — Wed, 10 Jul 2019 08:58:18 GMT

I used the followint expression in my solution above.

sum( aggr( -1* ( only(LL) > L OR only(HL) < L ), Element, [Element timestamp]) )

The script is as follows:

LOAD * INLINE [ Element,LL,HL a,100,150 b,20,24 ]; Transactions: LOAD Element, timestamp#([TimeStampField], 'D/M/YYYY h:mm') as [Element timestamp], Time(floor(timestamp#([TimeStampField], 'D/M/YYYY h:mm'), 10 / 1440)) as [Element time], L inline [ Element, TimeStampField, L b,6/1/2019 0:00,23 b,6/1/2019 0:01,12 b,6/1/2019 0:02,32 b,6/1/2019 0:03,45 b,6/1/2019 0:04,65 b,6/1/2019 0:05,34 b,6/1/2019 0:06,76 b,6/1/2019 0:07,27 b,6/1/2019 0:08,12 b,6/1/2019 0:09,32 b,6/1/2019 0:10,14 b,6/1/2019 0:21,25 b,6/1/2019 0:11,14 b,6/1/2019 0:12,35 b,6/1/2019 0:13,48 b,6/1/2019 0:14,61 b,6/1/2019 0:15,32 b,6/1/2019 0:16,73 b,6/1/2019 0:17,24 b,6/1/2019 0:18,15 b,6/1/2019 0:19,36 b,6/1/2019 0:20,12 a,6/1/2019 0:00,123 a,6/1/2019 0:01,212 a,6/1/2019 0:02,332 a,6/1/2019 0:03,145 a,6/1/2019 0:04,265 a,6/1/2019 0:05,334 a,6/1/2019 0:06,376 a,6/1/2019 0:07,227 a,6/1/2019 0:08,112 a,6/1/2019 0:09,132 a,6/1/2019 0:10,314 a,6/1/2019 0:21,225 a,6/1/2019 0:11,114 a,6/1/2019 0:12,235 a,6/1/2019 0:13,248 a,6/1/2019 0:14,261 a,6/1/2019 0:15,132 a,6/1/2019 0:16,373 a,6/1/2019 0:17,224 a,6/1/2019 0:18,115 a,6/1/2019 0:19,336 a,6/1/2019 0:20,212];

Re: counting time difference between dates in the same column

Brett_Bleess — Wed, 31 Jul 2019 17:47:15 GMT

Did Vegar's posts help you resolve your issue? If so, please be sure to give him credit by clicking the Accept as Solution button on the post(s) that helped, or you can leave a reply confirming they did, and we can mark things. If you are still trying to figure things out, please leave an update with where things stand.

Regards,
Brett