Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
MAR | Different | Name |
150601SCBLAEADBXXX0734857517 | 1:05:22 | You,Zijun |
150630SCBLAEADBXXX0739036592 | 8:59:38 | Yi,Tianming |
150630SCBLAEADBXXX0739036596 | 8:58:38 | Yi,Tianming |
150630SCBLAEADBXXX0739036600 | 8:56:48 | Yi,Tianming |
150630SCBLAEADBXXX0739036979 | 6:07:46 | Yi,Tianming |
150701SCBLAEADBXXX0739037172 | 4:37:12 | Yi,Tianming |
150701SCBLAEADBXXX0739037172 | 4:37:12 | Yi,Tianming |
150701SCBLAEADBXXX0739037178 | 4:34:59 | Yi,Tianming |
150701SCBLAEADBXXX0739037178 | 4:34:59 | Yi,Tianming |
150701SCBLAEADBXXX0739037178 | 4:34:59 | Yi,Tianming |
20150601OL01201505310526 | 0:02:05 | You,Zijun |
20150601OL02001505310492 | 0:02:34 | You,Zijun |
20150601OL02001505310535 | 0:01:47 | You,Zijun |
20150601OL02001505310541 | 0:01:37 | You,Zijun |
20150601OL02001505310562 | 0:01:01 | Qian,Lin |
20150601OL02001505310577 | 0:00:19 | You,Zijun |
20150601OL02001505310629 | 0:00:27 | Qian,Lin |
20150601OL02101505310454 | 0:02:32 | You,Zijun |
20150601OL02101505310460 | 0:02:28 | You,Zijun |
20150601OL02101505310476 | 0:02:30 | You,Zijun |
20150601OL02101505310484 | 0:02:33 | You,Zijun |
20150601OL02401505310529 | 0:02:03 | Qian,Lin |
20150601OL02801505310582 | 0:00:18 | Qian,Lin |
20150601OL02901505310538 | 0:01:51 | Qian,Lin |
20150601OL02901505310548 | 0:01:24 | You,Zijun |
20150601OL02901505310550 | 0:01:25 | Qian,Lin |
=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))
Very nice Manish
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)
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)