topic Re: Time dimension in Set Analysis in QlikView

topic Re: Time dimension in Set Analysis in QlikView https://community.qlik.com/t5/QlikView/Time-dimension-in-Set-Analysis/m-p/922586#M528972 <HTML><HEAD></HEAD><BODY><P>script:</P><P></P><P><EM>LOAD </EM></P><P><EM>  ROWNO() AS ID,</EM></P><P><EM>  MAR, </EM></P><P><EM>    Different, </EM></P><P><EM>    Name</EM></P><P><EM>FROM</EM></P><P><EM><SPAN>[</SPAN><A class="jive-link-thread-small" data-containerid="2052" data-containertype="14" data-objectid="176454" data-objecttype="1" href="https://community.qlik.com/thread/176454">https://community.qlik.com/thread/176454</A><SPAN>]</SPAN></EM></P><P><EM>(html, codepage is 1252, embedded labels, table is @1);</EM></P><P></P><P></P><P>expression</P><P></P><P><EM>count({$ <ID={"=num(Different)>=5/(24*60)"}>} DISTINCT MAR)</EM></P><P>or</P><P><EM>count({$ <ID={"=num(Different)>=5/(24*60)"}>}  MAR)</EM></P><P></P><P><IMG alt="1.png" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/95940_1.png" style="height: auto; width: auto;" /></P></BODY></HTML> Thu, 13 Aug 2015 12:20:13 GMT maxgro 2015-08-13T12:20:13Z Time dimension in Set Analysis https://community.qlik.com/t5/QlikView/Time-dimension-in-Set-Analysis/m-p/922583#M528969 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P></P><P>Below is my data .I need to find count of Mar based on time dimension.</P><P></P><P>In the below scenario i need to find count of Mar where the different is less than 0:05:00</P><P></P><P></P><P>    </P><TABLE border="0" cellpadding="0" cellspacing="0" width="309"><TBODY><TR><TD class="xl63" height="17" width="192">MAR</TD><TD class="xl63" style="border-left: none;" width="54">Different</TD><TD class="xl63" style="border-left: none;" width="63">Name</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">150601SCBLAEADBXXX0734857517</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">1:05:22</TD><TD class="xl66" style="border-top: none; border-left: none;">You,Zijun</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">150630SCBLAEADBXXX0739036592</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">8:59:38</TD><TD class="xl66" style="border-top: none; border-left: none;">Yi,Tianming</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">150630SCBLAEADBXXX0739036596</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">8:58:38</TD><TD class="xl66" style="border-top: none; border-left: none;">Yi,Tianming</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">150630SCBLAEADBXXX0739036600</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">8:56:48</TD><TD class="xl66" style="border-top: none; border-left: none;">Yi,Tianming</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">150630SCBLAEADBXXX0739036979</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">6:07:46</TD><TD class="xl66" style="border-top: none; border-left: none;">Yi,Tianming</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">150701SCBLAEADBXXX0739037172</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">4:37:12</TD><TD class="xl66" style="border-top: none; border-left: none;">Yi,Tianming</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">150701SCBLAEADBXXX0739037172</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">4:37:12</TD><TD class="xl66" style="border-top: none; border-left: none;">Yi,Tianming</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">150701SCBLAEADBXXX0739037178</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">4:34:59</TD><TD class="xl66" style="border-top: none; border-left: none;">Yi,Tianming</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">150701SCBLAEADBXXX0739037178</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">4:34:59</TD><TD class="xl66" style="border-top: none; border-left: none;">Yi,Tianming</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">150701SCBLAEADBXXX0739037178</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">4:34:59</TD><TD class="xl66" style="border-top: none; border-left: none;">Yi,Tianming</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">20150601OL01201505310526</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0:02:05</TD><TD class="xl66" style="border-top: none; border-left: none;">You,Zijun</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">20150601OL02001505310492</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0:02:34</TD><TD class="xl66" style="border-top: none; border-left: none;">You,Zijun</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">20150601OL02001505310535</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0:01:47</TD><TD class="xl66" style="border-top: none; border-left: none;">You,Zijun</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">20150601OL02001505310541</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0:01:37</TD><TD class="xl66" style="border-top: none; border-left: none;">You,Zijun</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">20150601OL02001505310562</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0:01:01</TD><TD class="xl66" style="border-top: none; border-left: none;">Qian,Lin</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">20150601OL02001505310577</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0:00:19</TD><TD class="xl66" style="border-top: none; border-left: none;">You,Zijun</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">20150601OL02001505310629</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0:00:27</TD><TD class="xl66" style="border-top: none; border-left: none;">Qian,Lin</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">20150601OL02101505310454</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0:02:32</TD><TD class="xl66" style="border-top: none; border-left: none;">You,Zijun</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">20150601OL02101505310460</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0:02:28</TD><TD class="xl66" style="border-top: none; border-left: none;">You,Zijun</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">20150601OL02101505310476</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0:02:30</TD><TD class="xl66" style="border-top: none; border-left: none;">You,Zijun</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">20150601OL02101505310484</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0:02:33</TD><TD class="xl66" style="border-top: none; border-left: none;">You,Zijun</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">20150601OL02401505310529</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0:02:03</TD><TD class="xl66" style="border-top: none; border-left: none;">Qian,Lin</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">20150601OL02801505310582</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0:00:18</TD><TD class="xl66" style="border-top: none; border-left: none;">Qian,Lin</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">20150601OL02901505310538</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0:01:51</TD><TD class="xl66" style="border-top: none; border-left: none;">Qian,Lin</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">20150601OL02901505310548</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0:01:24</TD><TD class="xl66" style="border-top: none; border-left: none;">You,Zijun</TD></TR><TR><TD class="xl64" height="17" style="border-top: none;">20150601OL02901505310550</TD><TD align="right" class="xl65" style="border-top: none; border-left: none;">0:01:25</TD><TD class="xl66" style="border-top: none; border-left: none;">Qian,Lin</TD></TR></TBODY></TABLE></BODY></HTML> Thu, 13 Aug 2015 10:59:29 GMT https://community.qlik.com/t5/QlikView/Time-dimension-in-Set-Analysis/m-p/922583#M528969 2015-08-13T10:59:29Z Re: Time dimension in Set Analysis https://community.qlik.com/t5/QlikView/Time-dimension-in-Set-Analysis/m-p/922584#M528970 <HTML><HEAD></HEAD><BODY><P>=COUNT(Aggr(IF(Time#(Different,'h:mm:ss') <= Time#('00:05:00','hh:mm:ss'),MAR),Different))</P><P>or</P><P>=COUNT(DISTINCT Aggr(IF(Time#(Different,'h:mm:ss') <= Time#('00:05:00'<SPAN style="font-size: 13.3333330154419px;">,'hh:mm:ss'</SPAN>),MAR),Different))</P></BODY></HTML> Thu, 13 Aug 2015 11:07:35 GMT https://community.qlik.com/t5/QlikView/Time-dimension-in-Set-Analysis/m-p/922584#M528970 MK_QSL 2015-08-13T11:07:35Z Re: Time dimension in Set Analysis https://community.qlik.com/t5/QlikView/Time-dimension-in-Set-Analysis/m-p/922585#M528971 <HTML><HEAD></HEAD><BODY><P>Very nice Manish <IMG src="https://community.qlik.com/legacyfs/online/emoticons/happy.png" /></P></BODY></HTML> Thu, 13 Aug 2015 11:26:12 GMT https://community.qlik.com/t5/QlikView/Time-dimension-in-Set-Analysis/m-p/922585#M528971 sunny_talwar 2015-08-13T11:26:12Z Re: Time dimension in Set Analysis https://community.qlik.com/t5/QlikView/Time-dimension-in-Set-Analysis/m-p/922586#M528972 <HTML><HEAD></HEAD><BODY><P>script:</P><P></P><P><EM>LOAD </EM></P><P><EM>  ROWNO() AS ID,</EM></P><P><EM>  MAR, </EM></P><P><EM>    Different, </EM></P><P><EM>    Name</EM></P><P><EM>FROM</EM></P><P><EM><SPAN>[</SPAN><A class="jive-link-thread-small" data-containerid="2052" data-containertype="14" data-objectid="176454" data-objecttype="1" href="https://community.qlik.com/thread/176454">https://community.qlik.com/thread/176454</A><SPAN>]</SPAN></EM></P><P><EM>(html, codepage is 1252, embedded labels, table is @1);</EM></P><P></P><P></P><P>expression</P><P></P><P><EM>count({$ <ID={"=num(Different)>=5/(24*60)"}>} DISTINCT MAR)</EM></P><P>or</P><P><EM>count({$ <ID={"=num(Different)>=5/(24*60)"}>}  MAR)</EM></P><P></P><P><IMG alt="1.png" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/95940_1.png" style="height: auto; width: auto;" /></P></BODY></HTML> Thu, 13 Aug 2015 12:20:13 GMT https://community.qlik.com/t5/QlikView/Time-dimension-in-Set-Analysis/m-p/922586#M528972 maxgro 2015-08-13T12:20:13Z Re: Time dimension in Set Analysis https://community.qlik.com/t5/QlikView/Time-dimension-in-Set-Analysis/m-p/922587#M528973 <HTML><HEAD></HEAD><BODY><P>This is also working, will it create problem if not aggregated before count? Thanks in advance.</P><P>=COUNT({<Different = {"<=$(=Time#('0:05:00','h:mm:ss'))"}  >} MAR)</P></BODY></HTML> Thu, 13 Aug 2015 12:51:35 GMT https://community.qlik.com/t5/QlikView/Time-dimension-in-Set-Analysis/m-p/922587#M528973 Digvijay_Singh 2015-08-13T12:51:35Z