Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Time dimension in Set Analysis

Hi,

Below is my data .I need to find count of Mar based on time dimension.

In the below scenario i need to find count of Mar where the different is less than 0:05:00

   

MARDifferentName
150601SCBLAEADBXXX07348575171:05:22You,Zijun
150630SCBLAEADBXXX07390365928:59:38Yi,Tianming
150630SCBLAEADBXXX07390365968:58:38Yi,Tianming
150630SCBLAEADBXXX07390366008:56:48Yi,Tianming
150630SCBLAEADBXXX07390369796:07:46Yi,Tianming
150701SCBLAEADBXXX07390371724:37:12Yi,Tianming
150701SCBLAEADBXXX07390371724:37:12Yi,Tianming
150701SCBLAEADBXXX07390371784:34:59Yi,Tianming
150701SCBLAEADBXXX07390371784:34:59Yi,Tianming
150701SCBLAEADBXXX07390371784:34:59Yi,Tianming
20150601OL012015053105260:02:05You,Zijun
20150601OL020015053104920:02:34You,Zijun
20150601OL020015053105350:01:47You,Zijun
20150601OL020015053105410:01:37You,Zijun
20150601OL020015053105620:01:01Qian,Lin
20150601OL020015053105770:00:19You,Zijun
20150601OL020015053106290:00:27Qian,Lin
20150601OL021015053104540:02:32You,Zijun
20150601OL021015053104600:02:28You,Zijun
20150601OL021015053104760:02:30You,Zijun
20150601OL021015053104840:02:33You,Zijun
20150601OL024015053105290:02:03Qian,Lin
20150601OL028015053105820:00:18Qian,Lin
20150601OL029015053105380:01:51Qian,Lin
20150601OL029015053105480:01:24You,Zijun
20150601OL029015053105500:01:25Qian,Lin
4 Replies
MK_QSL
MVP
MVP

=COUNT(Aggr(IF(Time#(Different,'h:mm:ss') <= Time#('00:05:00','hh:mm:ss'),MAR),Different))

or

=COUNT(DISTINCT Aggr(IF(Time#(Different,'h:mm:ss') <= Time#('00:05:00','hh:mm:ss'),MAR),Different))

sunny_talwar

Very nice Manish

maxgro
MVP
MVP

script:

LOAD

  ROWNO() AS ID,

  MAR,

    Different,

    Name

FROM

[https://community.qlik.com/thread/176454]

(html, codepage is 1252, embedded labels, table is @1);

expression

count({$ <ID={"=num(Different)>=5/(24*60)"}>} DISTINCT MAR)

or

count({$ <ID={"=num(Different)>=5/(24*60)"}>}  MAR)

1.png

Digvijay_Singh

This is also working, will it create problem if not aggregated before count? Thanks in advance.

=COUNT({<Different = {"<=$(=Time#('0:05:00','h:mm:ss'))"}  >} MAR)