Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
Qliksense7
Contributor II
Contributor II

counting time difference between dates in the same column

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!

ElementsPeriodvalue
a6/1/2019 0:00345
a6/1/2019 0:01456
a6/1/2019 0:02323
a6/1/2019 0:03322
a6/1/2019 0:04345
a6/1/2019 0:05432
a6/1/2019 0:06312
a6/1/2019 0:07345
a6/1/2019 0:08234
a6/1/2019 0:09334
a6/1/2019 0:10356
5 Replies
Vegar
Partner
Partner

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)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Qliksense7
Contributor II
Contributor II

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

Vegar
Partner
Partner

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?

image.png

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Vegar
Partner
Partner

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];

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Brett_Bleess
Support (Former)
Support (Former)

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.