topic Re: Skip 0 values before making the calculated dimension in QlikView

topic Re: Skip 0 values before making the calculated dimension in QlikView https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228535#M868940 <HTML><HEAD></HEAD><BODY><P>All right,so please mark it as corrent answer,thanks.</P></BODY></HTML> Fri, 25 Nov 2016 10:23:09 GMT 2016-11-25T10:23:09Z Skip 0 values before making the calculated dimension https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228521#M868921 <HTML><HEAD></HEAD><BODY><P>Hello,</P><P></P><P>I  have the following calculated dimension:</P><P></P><P>=aggr(if(rank(sum(sales),4)<0.1*count(total distinct customer), '0-10%',</P><P>if(rank(sum(sales),4)<0.2*count(total distinct customer), '10-20%',</P><P>if(rank(sum(sales),4)<0.3*count(total distinct customer), '20-30%',</P><P>if(rank(sum(sales),4)<0.4*count(total distinct customer), '30-40%',</P><P>if(rank(sum(sales),4)<0.5*count(total distinct customer), '40-50%',</P><P>if(rank(sum(sales),4)<0.6*count(total distinct customer), '50-60%',</P><P>if(rank(sum(sales),4)<0.7*count(total distinct customer), '60-70%',</P><P>if(rank(sum(sales),4)<0.8*count(total distinct customer), '70-80%',</P><P>if(rank(sum(sales),4)<0.9*count(total distinct customer), '80-90%'</P><P>, '90-100%'))))))))),customer).</P><P></P><P>I have Rows with 0 value, however i need them for the qwd.</P><P></P><P>¿it is posible to eliminate the values with sum 0?</P></BODY></HTML> Wed, 25 Nov 2020 16:16:04 GMT https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228521#M868921 Anonymous 2020-11-25T16:16:04Z Re: Skip 0 values before making the calculated dimension https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228522#M868922 <HTML><HEAD></HEAD><BODY><P>You could add another if I guess.</P><P>But why don't you use the class function?</P></BODY></HTML> Fri, 25 Nov 2016 08:40:14 GMT https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228522#M868922 giakoum 2016-11-25T08:40:14Z Re: Skip 0 values before making the calculated dimension https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228523#M868923 <HTML><HEAD></HEAD><BODY><P>i tried it i could not do it.</P><P></P><P>If you can help me  i would appreciate it.</P></BODY></HTML> Fri, 25 Nov 2016 08:44:30 GMT https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228523#M868923 Anonymous 2016-11-25T08:44:30Z Re: Skip 0 values before making the calculated dimension https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228524#M868924 <HTML><HEAD></HEAD><BODY><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">=if(sum(sales)=0, null(),</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">aggr(if(rank(sum(sales),4)<0.1*count(total distinct customer), '0-10%',</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(rank(sum(sales),4)<0.2*count(total distinct customer), '10-20%',</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(rank(sum(sales),4)<0.3*count(total distinct customer), '20-30%',</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(rank(sum(sales),4)<0.4*count(total distinct customer), '30-40%',</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(rank(sum(sales),4)<0.5*count(total distinct customer), '40-50%',</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(rank(sum(sales),4)<0.6*count(total distinct customer), '50-60%',</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(rank(sum(sales),4)<0.7*count(total distinct customer), '60-70%',</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(rank(sum(sales),4)<0.8*count(total distinct customer), '70-80%',</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(rank(sum(sales),4)<0.9*count(total distinct customer), '80-90%'</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">, '90-100%'))))))))),customer)</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">)</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;"></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">and check suppress when value is null</P></BODY></HTML> Fri, 25 Nov 2016 08:59:10 GMT https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228524#M868924 giakoum 2016-11-25T08:59:10Z Re: Skip 0 values before making the calculated dimension https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228525#M868925 <HTML><HEAD></HEAD><BODY><P>It gives me Allocated memory exceded</P></BODY></HTML> Fri, 25 Nov 2016 09:05:19 GMT https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228525#M868925 Anonymous 2016-11-25T09:05:19Z Re: Skip 0 values before making the calculated dimension https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228526#M868926 <HTML><HEAD></HEAD><BODY><P>I do not know what i have touched but with the formula you game me it gives this table<IMG alt="pareto.JPG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/144839_pareto.JPG" style="height: auto;" /> </P><P>As you can see from 40% to 90% have dissapeared, however i would like to have the formula only with data not 0 and null so it doesn´t dissapeare those 'ranges'.</P></BODY></HTML> Fri, 25 Nov 2016 09:26:15 GMT https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228526#M868926 Anonymous 2016-11-25T09:26:15Z Re: Skip 0 values before making the calculated dimension https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228527#M868927 <HTML><HEAD></HEAD><BODY><P>my mistake, replace <SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px; background-color: #f2f2f2;">sum(sales) to aggr(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px; background-color: #f2f2f2;">sum(sales), customer)</SPAN></SPAN></P><P></P><P>but I really suggest that you use class</P></BODY></HTML> Fri, 25 Nov 2016 09:34:03 GMT https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228527#M868927 giakoum 2016-11-25T09:34:03Z Re: Skip 0 values before making the calculated dimension https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228528#M868928 <HTML><HEAD></HEAD><BODY><P>where should it be that customer? it is giving problems.</P><P></P><P>how could it be done this with class?</P><P></P><P>class(client,10)?? i do not understand this function </P></BODY></HTML> Fri, 25 Nov 2016 09:39:40 GMT https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228528#M868928 Anonymous 2016-11-25T09:39:40Z Re: Skip 0 values before making the calculated dimension https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228529#M868929 <HTML><HEAD></HEAD><BODY><P>Please post a sample app</P></BODY></HTML> Fri, 25 Nov 2016 09:45:47 GMT https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228529#M868929 giakoum 2016-11-25T09:45:47Z Re: Skip 0 values before making the calculated dimension https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228530#M868932 <HTML><HEAD></HEAD><BODY><P>Try something like below</P><P></P><P><SPAN style="color: #222222; font-family: arial, sans-serif; font-size: small;"><STRONG>=Replace(Aggr(Class(Sum(Sales)</STRONG></SPAN><SPAN style="color: #222222; font-family: arial, sans-serif; font-size: small;"><STRONG>*100, 10), customer), '<= x <', ' - ')</STRONG></SPAN></P></BODY></HTML> Fri, 25 Nov 2016 09:47:01 GMT https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228530#M868932 Anil_Babu_Samineni 2016-11-25T09:47:01Z Re: Skip 0 values before making the calculated dimension https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228531#M868936 <HTML><HEAD></HEAD><BODY><P>Hello,</P><P></P><P>on the excel ist shows the data what i would like to have.</P><P></P><P>Thank you </P></BODY></HTML> Fri, 25 Nov 2016 09:58:07 GMT https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228531#M868936 Anonymous 2016-11-25T09:58:07Z Re: Skip 0 values before making the calculated dimension https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228532#M868937 <HTML><HEAD></HEAD><BODY><P>Hello, doing your dimension expression it gives me a lot of classes.</P><P></P><P>could it be done so i have from 0-10%, 10 to 20% and so on?</P></BODY></HTML> Fri, 25 Nov 2016 10:00:32 GMT https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228532#M868937 Anonymous 2016-11-25T10:00:32Z Re: Skip 0 values before making the calculated dimension https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228533#M868938 <HTML><HEAD></HEAD><BODY><P>try:</P><P></P><P>=aggr(</P><P>    if(rank(sum({<customer-={"=sum(sales)=0"}>}sales),4)<0.1*count({<customer-={"=sum(sales)=0"}>}total distinct customer), '0-10%',</P><P>    if(rank(sum({<customer-={"=sum(sales)=0"}>}sales),4)<0.2*count({<customer-={"=sum(sales)=0"}>}total distinct customer), '10-20%',</P><P>    if(rank(sum({<customer-={"=sum(sales)=0"}>}sales),4)<0.3*count({<customer-={"=sum(sales)=0"}>}total distinct customer), '20-30%',</P><P>    if(rank(sum({<customer-={"=sum(sales)=0"}>}sales),4)<0.4*count({<customer-={"=sum(sales)=0"}>}total distinct customer), '30-40%',</P><P>    if(rank(sum({<customer-={"=sum(sales)=0"}>}sales),4)<0.5*count({<customer-={"=sum(sales)=0"}>}total distinct customer), '40-50%',</P><P>    if(rank(sum({<customer-={"=sum(sales)=0"}>}sales),4)<0.6*count({<customer-={"=sum(sales)=0"}>}total distinct customer), '50-60%',</P><P>    if(rank(sum({<customer-={"=sum(sales)=0"}>}sales),4)<0.7*count({<customer-={"=sum(sales)=0"}>}total distinct customer), '60-70%',</P><P>    if(rank(sum({<customer-={"=sum(sales)=0"}>}sales),4)<0.8*count({<customer-={"=sum(sales)=0"}>}total distinct customer), '70-80%',</P><P>    if(rank(sum({<customer-={"=sum(sales)=0"}>}sales),4)<0.9*count({<customer-={"=sum(sales)=0"}>}total distinct customer), '80-90%'</P><P>    ,'90-100%')))))))))</P><P>,customer</P><P>)</P></BODY></HTML> Fri, 25 Nov 2016 10:00:36 GMT https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228533#M868938 2016-11-25T10:00:36Z Re: Skip 0 values before making the calculated dimension https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228534#M868939 <HTML><HEAD></HEAD><BODY><P>Thank you xmg it worked!!!</P><P></P><P>thank you to all</P></BODY></HTML> Fri, 25 Nov 2016 10:08:08 GMT https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228534#M868939 Anonymous 2016-11-25T10:08:08Z Re: Skip 0 values before making the calculated dimension https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228535#M868940 <HTML><HEAD></HEAD><BODY><P>All right,so please mark it as corrent answer,thanks.</P></BODY></HTML> Fri, 25 Nov 2016 10:23:09 GMT https://community.qlik.com/t5/QlikView/Skip-0-values-before-making-the-calculated-dimension/m-p/1228535#M868940 2016-11-25T10:23:09Z