Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
Showing results for 
Search instead for 
Did you mean: 
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!

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

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

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


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?


Plees ekskuse my Swenglish and or Norweglish spelling misstakes

I used the followint expression in my solution above.


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


The script is as follows:



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],
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
Digital Support
Digital Support

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.


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.