topic Calculate for N number of objects per week, how many days a KPI’s daily value crossed a threshold. in App Development
https://community.qlik.com/t5/App-Development/Calculate-for-N-number-of-objects-per-week-how-many-days-a-KPI-s/m-p/1746604#M57329
<P>Hey Qliksters,</P><P> </P><P>I have a use-case where a user wants to calculate for N number of objects per week, how many days a KPI’s daily value crossed a threshold.</P><P> </P><P>As an example:</P><P>Given these rows below, in a separate table, calculate per object per week how many days had a value greater than 3.</P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 895px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/41096i4277A5817DF1139C/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P><P>Some conditions/things to keep in mind:</P><OL><LI>The actual data is at the 15 minute level, so I don’t think we can just write: <STRONG>count({<Fact1 = {">3"}>}distinct Date)</STRONG> because the threshold is based on the daily value)</LI><LI>There are 800 million+ fact rows in the actual data set, so it would not be ideal to have a unique key per row of data to "force" the set analysis to calculate row-by-row. I tried and hit a calculation timeout<UL><LI><STRONG>count({<uniqueRowKey= {"= sum(aggr(sum(Fact1),uniqueRowKey)) > 3"}>}distinct Date)</STRONG></LI></UL></LI></OL><P> </P><P>Any tips and tricks?</P><P> </P><P>Thanks!!!</P><P> </P><P> </P><P>S</P>Wed, 23 Sep 2020 23:54:25 GMTSazabi2020-09-23T23:54:25ZCalculate for N number of objects per week, how many days a KPI’s daily value crossed a threshold.
https://community.qlik.com/t5/App-Development/Calculate-for-N-number-of-objects-per-week-how-many-days-a-KPI-s/m-p/1746604#M57329
<P>Hey Qliksters,</P><P> </P><P>I have a use-case where a user wants to calculate for N number of objects per week, how many days a KPI’s daily value crossed a threshold.</P><P> </P><P>As an example:</P><P>Given these rows below, in a separate table, calculate per object per week how many days had a value greater than 3.</P><DIV class="mceNonEditable lia-copypaste-placeholder"> </DIV><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 895px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/41096i4277A5817DF1139C/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P><P>Some conditions/things to keep in mind:</P><OL><LI>The actual data is at the 15 minute level, so I don’t think we can just write: <STRONG>count({<Fact1 = {">3"}>}distinct Date)</STRONG> because the threshold is based on the daily value)</LI><LI>There are 800 million+ fact rows in the actual data set, so it would not be ideal to have a unique key per row of data to "force" the set analysis to calculate row-by-row. I tried and hit a calculation timeout<UL><LI><STRONG>count({<uniqueRowKey= {"= sum(aggr(sum(Fact1),uniqueRowKey)) > 3"}>}distinct Date)</STRONG></LI></UL></LI></OL><P> </P><P>Any tips and tricks?</P><P> </P><P>Thanks!!!</P><P> </P><P> </P><P>S</P>Wed, 23 Sep 2020 23:54:25 GMThttps://community.qlik.com/t5/App-Development/Calculate-for-N-number-of-objects-per-week-how-many-days-a-KPI-s/m-p/1746604#M57329Sazabi2020-09-23T23:54:25Z