topic Aggr with If Expression in QlikView

topic Aggr with If Expression in QlikView https://community.qlik.com/t5/QlikView/Aggr-with-If-Expression/m-p/9099#M607269 <HTML><HEAD></HEAD><BODY><P>Hi All,</P><P></P><P>Can any body help me to debug the issue in this expression : </P><P><SPAN style="font-size: 13.3333px;">pick(_order_bucket,</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  > vEGRevBucket2,</SPAN></P><P><SPAN style="font-size: 13.3333px;"> sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion,</SPAN></P><P><SPAN style="font-size: 13.3333px;"> </SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  <= vEGRevBucket2 and</SPAN></P><P><SPAN style="font-size: 13.3333px;"> sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  >= vEGRevBucket1, </SPAN></P><P><SPAN style="font-size: 13.3333px;"> sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion,</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  < vEGRevBucket1,</SPAN></P><P><SPAN style="font-size: 13.3333px;"> sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">)</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">I m trying to add it in Straight table to show 3 row in Straight table but only first one is showing number rest is showing Zero.</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">but when i write this each ode individiually than working fine in Text box.</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;"><A href="https://community.qlik.com/qlik-users/171708">stalwar1</A>‌</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">Regards,</SPAN></P><P><SPAN style="font-size: 13.3333px;">Vanraj<BR /></SPAN></P></BODY></HTML> Tue, 10 Apr 2018 06:25:03 GMT bohravanraj 2018-04-10T06:25:03Z Aggr with If Expression https://community.qlik.com/t5/QlikView/Aggr-with-If-Expression/m-p/9099#M607269 <HTML><HEAD></HEAD><BODY><P>Hi All,</P><P></P><P>Can any body help me to debug the issue in this expression : </P><P><SPAN style="font-size: 13.3333px;">pick(_order_bucket,</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  > vEGRevBucket2,</SPAN></P><P><SPAN style="font-size: 13.3333px;"> sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion,</SPAN></P><P><SPAN style="font-size: 13.3333px;"> </SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  <= vEGRevBucket2 and</SPAN></P><P><SPAN style="font-size: 13.3333px;"> sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  >= vEGRevBucket1, </SPAN></P><P><SPAN style="font-size: 13.3333px;"> sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion,</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  < vEGRevBucket1,</SPAN></P><P><SPAN style="font-size: 13.3333px;"> sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">)</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">I m trying to add it in Straight table to show 3 row in Straight table but only first one is showing number rest is showing Zero.</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">but when i write this each ode individiually than working fine in Text box.</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;"><A href="https://community.qlik.com/qlik-users/171708">stalwar1</A>‌</SPAN></P><P><SPAN style="font-size: 13.3333px;"><BR /></SPAN></P><P><SPAN style="font-size: 13.3333px;">Regards,</SPAN></P><P><SPAN style="font-size: 13.3333px;">Vanraj<BR /></SPAN></P></BODY></HTML> Tue, 10 Apr 2018 06:25:03 GMT https://community.qlik.com/t5/QlikView/Aggr-with-If-Expression/m-p/9099#M607269 bohravanraj 2018-04-10T06:25:03Z Re: Aggr with If Expression https://community.qlik.com/t5/QlikView/Aggr-with-If-Expression/m-p/9100#M607270 <HTML><HEAD></HEAD><BODY><P>Is _Order_bucket an island table? try this</P><P></P><P><STRONG>Pick(<SPAN style="color: #ff0000;">Only({1}</SPAN>_order_bucket<SPAN style="color: #ff0000;">)</SPAN>,</STRONG></P><P></P><P><STRONG>sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  > vEGRevBucket2,</STRONG></P><P><STRONG>sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion,</STRONG></P><P></P><P></P><P><STRONG>sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  <= vEGRevBucket2 and</STRONG></P><P><STRONG>sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  >= vEGRevBucket1,</STRONG></P><P><STRONG>sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion,</STRONG></P><P></P><P><STRONG>sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  < vEGRevBucket1,</STRONG></P><P><STRONG>sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion</STRONG></P><P></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"><STRONG>)</STRONG></P></BODY></HTML> Tue, 10 Apr 2018 08:59:02 GMT https://community.qlik.com/t5/QlikView/Aggr-with-If-Expression/m-p/9100#M607270 sunny_talwar 2018-04-10T08:59:02Z Re: Aggr with If Expression https://community.qlik.com/t5/QlikView/Aggr-with-If-Expression/m-p/9101#M607271 <HTML><HEAD></HEAD><BODY><P>Thanks a lot Sunny for quick reply.</P></BODY></HTML> Wed, 11 Apr 2018 03:20:37 GMT https://community.qlik.com/t5/QlikView/Aggr-with-If-Expression/m-p/9101#M607271 bohravanraj 2018-04-11T03:20:37Z Re: Aggr with If Expression https://community.qlik.com/t5/QlikView/Aggr-with-If-Expression/m-p/9102#M607272 <HTML><HEAD></HEAD><BODY><P>Will this work for Qlik Sense?</P><P>I'm Trying to do the same, but the third "record" always return 0 even if the formula is the same.</P><P>I have the same issue using "IF" . The Else, never works.</P><P><IMG alt="image1.png" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/204602_image1.png" style="height: auto;" /><IMG alt="image2.png" class="jive-image image-2" src="https://community.qlik.com/legacyfs/online/204603_image2.png" style="height: 59px; width: 620px;" /></P></BODY></HTML> Thu, 07 Jun 2018 14:39:52 GMT https://community.qlik.com/t5/QlikView/Aggr-with-If-Expression/m-p/9102#M607272 bernice_gonzale 2018-06-07T14:39:52Z Re: Aggr with If Expression https://community.qlik.com/t5/QlikView/Aggr-with-If-Expression/m-p/9103#M607273 <HTML><HEAD></HEAD><BODY><P>May be try adding NODISTINCT after Aggr() in your expression and see if that helps</P><P></P><P><STRONG>Sum(Aggr(NODISTINCT .....</STRONG></P></BODY></HTML> Thu, 07 Jun 2018 15:36:06 GMT https://community.qlik.com/t5/QlikView/Aggr-with-If-Expression/m-p/9103#M607273 sunny_talwar 2018-06-07T15:36:06Z Re: Aggr with If Expression https://community.qlik.com/t5/QlikView/Aggr-with-If-Expression/m-p/9104#M607274 <HTML><HEAD></HEAD><BODY><P>That fixed it! Thank you</P></BODY></HTML> Mon, 11 Jun 2018 21:13:14 GMT https://community.qlik.com/t5/QlikView/Aggr-with-If-Expression/m-p/9104#M607274 bernice_gonzale 2018-06-11T21:13:14Z