topic Re: Previous Value of the Expression in QlikView

topic Re: Previous Value of the Expression in QlikView https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884523#M308443 <HTML><HEAD></HEAD><BODY><P>Share QVW file</P></BODY></HTML> Thu, 09 Apr 2015 09:25:33 GMT 2015-04-09T09:25:33Z Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884519#M308439 <HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>Suppose I have a straight table with three dimensions and one expression, and the expression has nested If statement along with Fractile() function.</P><P></P><P><IMG alt="Straight_Table_1.PNG" class="jive-image image-1" src="https://community.qlik.com/legacyfs/online/83160_Straight_Table_1.PNG" style="height: auto;" /></P><P>When I select second row i.e. Test1, the value in the Metric is changing. </P><P></P><P>Our requirement is the Metric value should not change even if user selects any row in the straight table. Is there is any way, that I can get the previous value of the expression. I have tried Previous() and Only() function, but as the expression has nested If statements, the application hangs.</P><P></P><P>Waiting for inputs.</P><P></P><P>Thanks & Regards,</P><P>MK</P></BODY></HTML> Thu, 09 Apr 2015 09:17:07 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884519#M308439 Anonymous 2015-04-09T09:17:07Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884520#M308440 <HTML><HEAD></HEAD><BODY><P>What exactly the expression looks like? Would you be able to share that, because without looking at the expression, it would be hard to give any suggestions.</P><P></P><P>Best,</P><P>Sunny</P></BODY></HTML> Thu, 09 Apr 2015 09:19:48 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884520#M308440 sunny_talwar 2015-04-09T09:19:48Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884521#M308441 <HTML><HEAD></HEAD><BODY><P>When you select a row in a chart\table, you select corresponding values in the dimension(s). If you want this to have no effect on your calculations you need set analysis (see help). In this case that would be something like (example)</P><P></P><P>Sum({<Name,B,C>} Sales)</P><P></P><P>This disregards the selections in Name,B and C fields</P></BODY></HTML> Thu, 09 Apr 2015 09:22:48 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884521#M308441 stigchel 2015-04-09T09:22:48Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884522#M308442 <HTML><HEAD></HEAD><BODY><P>Thanks for the quick response <A href="https://community.qlik.com/qlik-users/171708">sunindia</A>‌,</P><P></P><P>The expression looks somewhat like -</P><P>=if(($(v1)+ $(v2)) <= fractile(TOTAL aggr(($(v1)+$(v2)</P><P>),dimenaion1), 0.1), 10,</P><P>if(($(v1)+$(v2)) <= fractile(TOTAL aggr(($(v1)+$(v2)</P><P>),dimenaion1), 0.2), 12, </P><P> if(($(v1)+$(v2)) <= fractile(TOTAL aggr(($(v1)+$(v2)</P><P>),dimenaion1), 0.3), 14, </P><P> if(($(v1)+$(v2)) <= fractile(TOTAL aggr(($(v1)+$(v2)</P><P>),dimenaion1), 0.4), 16,  </P><P> if(($(v1)+$(v2)) <= fractile(TOTAL aggr(($(v1)+$(v2)</P><P>),dimenaion1), 0.5), 18,  </P><P> if(($(v1)+$(v2)) <= fractile(TOTAL aggr(($(v1)+$(v2)</P><P>),dimenaion1), 0.6), 20,  </P><P> if(($(v1)+$(v2)) <= fractile(TOTAL aggr(($(v1)+$(v2)</P><P>),dimenaion1), 0.7), 22,  </P><P> if(($(v1)+$(v2)) <= fractile(TOTAL aggr(($(v1)+$(v2)</P><P>),dimenaion1), 0.8), 24, </P><P> if(($(v1)+$(v2)) <= fractile(TOTAL aggr(($(v1)+$(v2)</P><P>),dimenaion1), 0.9), 26,  </P><P>if(($(v1)+$(v2)) <= fractile(TOTAL aggr(($(v1)+$(v2)</P><P>),dimenaion1), 1.0),28, 8 ))))))))))</P><P></P><P>Thus, when I select only one row, by default the value becomes 10.</P><P></P><P>Please assist.</P><P></P><P>Thanks & Regards,</P><P>MK</P></BODY></HTML> Thu, 09 Apr 2015 09:24:20 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884522#M308442 Anonymous 2015-04-09T09:24:20Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884523#M308443 <HTML><HEAD></HEAD><BODY><P>Share QVW file</P></BODY></HTML> Thu, 09 Apr 2015 09:25:33 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884523#M308443 2015-04-09T09:25:33Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884524#M308444 <HTML><HEAD></HEAD><BODY><P>Try this:</P><P></P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">The expression looks somewhat like -</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">=if(($(v1)+ $(v2)) <= fractile({<Name = , B = , C =>}TOTAL aggr(($(v1)+$(v2)</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">),dimenaion1), 0.1), 10,</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(($(v1)+$(v2)) <= fractile(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">{<Name = , B = , C =>}</SPAN>TOTAL aggr(($(v1)+$(v2)</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">),dimenaion1), 0.2), 12,</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(($(v1)+$(v2)) <= fractile(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">{<Name = , B = , C =>}</SPAN>TOTAL aggr(($(v1)+$(v2)</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">),dimenaion1), 0.3), 14,</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(($(v1)+$(v2)) <= fractile(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">{<Name = , B = , C =>}</SPAN>TOTAL aggr(($(v1)+$(v2)</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">),dimenaion1), 0.4), 16, </P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(($(v1)+$(v2)) <= fractile(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">{<Name = , B = , C =>}</SPAN>TOTAL aggr(($(v1)+$(v2)</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">),dimenaion1), 0.5), 18, </P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(($(v1)+$(v2)) <= fractile(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">{<Name = , B = , C =>}</SPAN>TOTAL aggr(($(v1)+$(v2)</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">),dimenaion1), 0.6), 20, </P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(($(v1)+$(v2)) <= fractile(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">{<Name = , B = , C =>}</SPAN>TOTAL aggr(($(v1)+$(v2)</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">),dimenaion1), 0.7), 22, </P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(($(v1)+$(v2)) <= fractile(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">{<Name = , B = , C =>}</SPAN>TOTAL aggr(($(v1)+$(v2)</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">),dimenaion1), 0.8), 24,</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(($(v1)+$(v2)) <= fractile(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">{<Name = , B = , C =>}</SPAN>TOTAL aggr(($(v1)+$(v2)</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">),dimenaion1), 0.9), 26, </P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">if(($(v1)+$(v2)) <= fractile(<SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">{<Name = , B = , C =>}</SPAN>TOTAL aggr(($(v1)+$(v2)</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">),dimenaion1), 1.0),28, 8 ))))))))))</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;">Best,</P><P style="font-size: 13px; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; color: #3d3d3d;">Sunny</P></BODY></HTML> Thu, 09 Apr 2015 09:28:13 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884524#M308444 sunny_talwar 2015-04-09T09:28:13Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884525#M308445 <HTML><HEAD></HEAD><BODY><P>I tried with your expression, but no luck.</P><P></P><P>The Metric is depending on the Name dimension, in that case, I cant ignore that as well.</P><P></P><P>Please assist.</P></BODY></HTML> Thu, 09 Apr 2015 09:29:28 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884525#M308445 Anonymous 2015-04-09T09:29:28Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884526#M308446 <HTML><HEAD></HEAD><BODY><P>Tried this way, as well, no luck. <IMG src="https://community.qlik.com/legacyfs/online/emoticons/sad.png" /></P></BODY></HTML> Thu, 09 Apr 2015 09:31:41 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884526#M308446 Anonymous 2015-04-09T09:31:41Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884527#M308447 <HTML><HEAD></HEAD><BODY><P>It's not ignoring the Dimension, it's ignoring selections in the dimensions. See also Sunny's answer</P></BODY></HTML> Thu, 09 Apr 2015 09:32:31 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884527#M308447 stigchel 2015-04-09T09:32:31Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884528#M308448 <HTML><HEAD></HEAD><BODY><P>Then probably whatever is in <SPAN style="color: #3d3d3d; font-family: 'Helvetica Neue', Helvetica, Arial, 'Lucida Grande', sans-serif; font-size: 13px;">$(v1)+$(v2) is also affected by the selection, use the same set analysis in these as well. Otherwise share your qvw</SPAN></P></BODY></HTML> Thu, 09 Apr 2015 09:38:56 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884528#M308448 stigchel 2015-04-09T09:38:56Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884529#M308449 <HTML><HEAD></HEAD><BODY><P>Thanks all for the replies, please find attached the sample qvw for same. </P></BODY></HTML> Thu, 09 Apr 2015 09:41:37 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884529#M308449 Anonymous 2015-04-09T09:41:37Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884530#M308450 <HTML><HEAD></HEAD><BODY><P>PFA th qvw file</P></BODY></HTML> Thu, 09 Apr 2015 09:42:13 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884530#M308450 Anonymous 2015-04-09T09:42:13Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884531#M308451 <HTML><HEAD></HEAD><BODY><P><SPAN style="font-size: 13.3333330154419px;">PFA th qvw file</SPAN></P></BODY></HTML> Thu, 09 Apr 2015 09:42:48 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884531#M308451 Anonymous 2015-04-09T09:42:48Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884532#M308452 <HTML><HEAD></HEAD><BODY><P>Can u share ur file?</P></BODY></HTML> Thu, 09 Apr 2015 09:45:42 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884532#M308452 nagarjuna_kotha 2015-04-09T09:45:42Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884533#M308453 <HTML><HEAD></HEAD><BODY><P>Try this </P><P>sum({$<Test1=>}Metric)</P></BODY></HTML> Thu, 09 Apr 2015 09:49:16 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884533#M308453 Anonymous 2015-04-09T09:49:16Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884534#M308454 <HTML><HEAD></HEAD><BODY><P>Try this formula:</P><P></P><P><STRONG>=if((C+B) <= fractile({<Name = , B = , C = >}TOTAL aggr((Only({<Name = , B = , C = >}C)+Only({<Name = , B = , C = >}B)),Name), 0.1), 10,</STRONG></P><P><STRONG> if((C+B) <= fractile({<Name = , B = , C = >}TOTAL aggr((Only({<Name = , B = , C = >}C)+Only({<Name = , B = , C = >}B)),Name), 0.2), 12, </STRONG></P><P><STRONG> if((C+B) <= fractile({<Name = , B = , C = >}TOTAL aggr((Only({<Name = , B = , C = >}C)+Only({<Name = , B = , C = >}B)),Name), 0.3), 14, </STRONG></P><P><STRONG> if((C+B) <= fractile({<Name = , B = , C = >}TOTAL aggr((Only({<Name = , B = , C = >}C)+Only({<Name = , B = , C = >}B)),Name), 0.4), 16,  </STRONG></P><P><STRONG> if((C+B) <= fractile({<Name = , B = , C = >}TOTAL aggr((Only({<Name = , B = , C = >}C)+Only({<Name = , B = , C = >}B)),Name), 0.5), 18,  </STRONG></P><P><STRONG> if((C+B) <= fractile({<Name = , B = , C = >}TOTAL aggr((Only({<Name = , B = , C = >}C)+Only({<Name = , B = , C = >}B)),Name), 0.6), 20,  </STRONG></P><P><STRONG> if((C+B) <= fractile({<Name = , B = , C = >}TOTAL aggr((Only({<Name = , B = , C = >}C)+Only({<Name = , B = , C = >}B)),Name), 0.7), 22,  </STRONG></P><P><STRONG> if((C+B) <= fractile({<Name = , B = , C = >}TOTAL aggr((Only({<Name = , B = , C = >}C)+Only({<Name = , B = , C = >}B)),Name), 0.8), 24, </STRONG></P><P><STRONG> if((C+B) <= fractile({<Name = , B = , C = >}TOTAL aggr((Only({<Name = , B = , C = >}C)+Only({<Name = , B = , C = >}B)),Name), 0.9), 26,  </STRONG></P><P><STRONG> if((C+B) <= fractile({<Name = , B = , C = >}TOTAL aggr((Only({<Name = , B = , C = >}C)+Only({<Name = , B = , C = >}B)),Name), 1.0), 28, 8))))))))))</STRONG></P></BODY></HTML> Thu, 09 Apr 2015 09:49:49 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884534#M308454 sunny_talwar 2015-04-09T09:49:49Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884535#M308455 <HTML><HEAD></HEAD><BODY><P>Try this</P><P></P><P>=if((Only({<Name,B,C>}C+B)) <= fractile({<Name,B,C>}TOTAL aggr((Only({<Name,B,C>} C+B)</P><P>),Name), 0.1), 10,</P><P>if((Only({<Name,B,C>} C+B)) <= fractile({<Name,B,C>}TOTAL aggr((Only({<Name,B,C>} C+B)</P><P>),Name), 0.2), 12, </P><P> if((Only({<Name,B,C>} C+B)) <= fractile({<Name,B,C>}TOTAL aggr((Only({<Name,B,C>} C+B)</P><P>),Name), 0.3), 14, </P><P> if((Only({<Name,B,C>} C+B)) <= fractile({<Name,B,C>}TOTAL aggr((Only({<Name,B,C>} C+B)</P><P>),Name), 0.4), 16,  </P><P> if((Only({<Name,B,C>} C+B)) <= fractile({<Name,B,C>}TOTAL aggr((Only({<Name,B,C>} C+B)</P><P>),Name), 0.5), 18,  </P><P> if((Only({<Name,B,C>} C+B)) <= fractile({<Name,B,C>}TOTAL aggr((Only({<Name,B,C>} C+B)</P><P>),Name), 0.6), 20,  </P><P> if((Only({<Name,B,C>} C+B)) <= fractile({<Name,B,C>}TOTAL aggr((Only({<Name,B,C>} C+B)</P><P>),Name), 0.7), 22,  </P><P> if((Only({<Name,B,C>} C+B)) <= fractile({<Name,B,C>}TOTAL aggr((Only({<Name,B,C>} C+B)</P><P>),Name), 0.8), 24, </P><P> if((Only({<Name,B,C>} C+B)) <= fractile({<Name,B,C>}TOTAL aggr((Only({<Name,B,C>} C+B)</P><P>),Name), 0.9), 26,  </P><P>if((Only({<Name,B,C>} C+B)) <= fractile({<Name,B,C>}TOTAL aggr((Only({<Name,B,C>} C+B)</P><P>),Name), 1.0),28,</P><P>      8 ))))))))))</P></BODY></HTML> Thu, 09 Apr 2015 10:00:00 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884535#M308455 stigchel 2015-04-09T10:00:00Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884536#M308456 <HTML><HEAD></HEAD><BODY><P>Thanks a lot <A href="https://community.qlik.com/qlik-users/171708">sunindia</A>‌.</P><P></P><P>It works.</P></BODY></HTML> Thu, 09 Apr 2015 10:02:27 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884536#M308456 Anonymous 2015-04-09T10:02:27Z Re: Previous Value of the Expression https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884537#M308457 <HTML><HEAD></HEAD><BODY><P>Not a problem bud, we are glad we were able to help <IMG src="https://community.qlik.com/legacyfs/online/emoticons/happy.png" />, right <A _jive_internal="true" href="https://community.qlik.com/people/Stigchel">Piet Hein van der Stigchel</A>‌?</P><P></P><P>Best,</P><P>Sunny</P></BODY></HTML> Thu, 09 Apr 2015 10:11:23 GMT https://community.qlik.com/t5/QlikView/Previous-Value-of-the-Expression/m-p/884537#M308457 sunny_talwar 2015-04-09T10:11:23Z