Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
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)