topic Re: *** Yet another Set Analysis Question *** in QlikView https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272609#M499105 <HTML><HEAD></HEAD><BODY><P>Mate, It happens some time for me also <IMG src="https://community.qlik.com/legacyfs/online/emoticons/cry.png" /> noworries.Cheers <IMG src="https://community.qlik.com/legacyfs/online/emoticons/happy.png" /></P></BODY></HTML> Wed, 08 Jun 2011 12:11:21 GMT 2011-06-08T12:11:21Z *** Yet another Set Analysis Question *** https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272598#M499094 <HTML><HEAD></HEAD><BODY><P><SPAN style="font-family: trebuchet ms,geneva;">Dear All,</SPAN></P><P></P><P><SPAN style="font-family: trebuchet ms,geneva;">I have quick question on one of the expressions. Here is the expression...</SPAN></P><P></P><P><SPAN style="color: #000080;"><STRONG style="font-family: trebuchet ms,geneva;">=SUM({1&lt;FYrNum = {$(=Only(FYrNum)-1)}, StoreName = {"*"}&gt;} NetSales) / 1000000</STRONG></SPAN></P><P></P><P><SPAN style="color: #000000; font-family: trebuchet ms,geneva;">I am sure this should be pretty simple to solve... but I am struggling to get my head around this one.</SPAN></P><P></P><P><STRONG style="color: #000000; font-family: trebuchet ms,geneva;">I have two list boxes : FYrNum &amp; StoreName</STRONG></P><P></P><P><SPAN style="color: #000000; font-family: trebuchet ms,geneva;">If the user selects 2010 in FYrNum (Listbox) I wanted to show the data for previous year, however user will also filter for the StoreName. My Current expression (above stated) doesn't filter on StoreName because we are using {1} and it just shows the data for all the stores. </SPAN></P><P></P><P><SPAN style="color: #000000; font-family: trebuchet ms,geneva;">So I need the data for FYrNum when the user selects the years and if the users selects the StoreName I wanted the data to be filtered on StoreName as well. I am using all this in one Guage chart.</SPAN></P><P></P><P><SPAN style="color: #000000; font-family: trebuchet ms,geneva;">I think I need to use SET OPERATOR to get this working but I am struggling to get this working.<BR /></SPAN></P><P></P><P><SPAN style="color: #000000; font-family: trebuchet ms,geneva;">Many thanks in anticipation.</SPAN></P><P></P><P><SPAN style="color: #000000; font-family: trebuchet ms,geneva;">Cheers - DV<BR /></SPAN></P></BODY></HTML> Wed, 08 Jun 2011 09:48:53 GMT https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272598#M499094 IAMDV 2011-06-08T09:48:53Z Re: *** Yet another Set Analysis Question *** https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272599#M499095 <HTML><HEAD></HEAD><BODY><P>Hello,</P><P></P><P>If I got your question right, you want your gauge chart to be affected by selections in both FYrNum and StoreName. Is that right? Then I'd use something like</P><P></P><PRE __default_attr="plain" __jive_macro_name="code" class="jive_text_macro jive_macro_code"><P>SUM({&lt; FYrNum = {$(=Only(FYrNum)-1)} &gt;} NetSales) / 1000000</P></PRE><P></P><P>Is this what you are expecting?</P><P></P><P><A _jive_internal="true" href="https://community.qlik.com/people/mabaeyens" style="font-size: 12px; outline-style: none; padding-top: 1px; padding-bottom: 1px; padding-left: 17px; color: #007fc0; zoom: 1; text-decoration: underline; background-position: no-repeat no-repeat;">Miguel Angel Baeyens</A></P><P>BI Consultant</P><P><A class="jive-link-external-small" href="http://www.grupocomex.com/" style="font-size: 12px; outline-style: none; color: #007fc0;">Comex Grupo Ibérica</A></P></BODY></HTML> Wed, 08 Jun 2011 10:07:50 GMT https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272599#M499095 Miguel_Angel_Baeyens 2011-06-08T10:07:50Z Re: *** Yet another Set Analysis Question *** https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272600#M499096 <HTML><HEAD></HEAD><BODY><P><SPAN style="font-family: trebuchet ms,geneva;">Miguel - Thank you for quick reponse. You got the question right... however, I tried your expression and it returns 0 instead of the previous year's NetSales. I am assuming that is because we are using current selection. Please help...</SPAN></P><P></P><P><SPAN style="font-family: trebuchet ms,geneva;">Thanks - <STRONG>DV</STRONG></SPAN></P></BODY></HTML> Wed, 08 Jun 2011 10:13:09 GMT https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272600#M499096 IAMDV 2011-06-08T10:13:09Z *** Yet another Set Analysis Question *** https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272601#M499097 <HTML><HEAD></HEAD><BODY><P> Hi </P><P></P><P>Use this expression</P><P></P><P>SUM({$&lt;FYrNum = {$(=Only(FYrNum)-1)}&gt;} NetSales) / 1000000</P></BODY></HTML> Wed, 08 Jun 2011 10:22:17 GMT https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272601#M499097 2011-06-08T10:22:17Z Re: *** Yet another Set Analysis Question *** https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272602#M499098 <HTML><HEAD></HEAD><BODY><P><SPAN style="font-family: trebuchet ms,geneva;">Bhaskar - This is same exactly same what Miguel has given. Even if you dont place the '$' at the beginning QV assumes it as current selection. So this is exactly same as Miguel's solution. The problem with this approach is... if I select FYrNum (Year) as 2010 then my current selection is for 2010 then how would get the data for previous year?</SPAN></P><P></P><P><SPAN style="font-family: trebuchet ms,geneva;">Hope this makes sense...</SPAN></P><P></P><P><SPAN style="font-family: trebuchet ms,geneva;">Cheers - <STRONG>DV</STRONG></SPAN></P></BODY></HTML> Wed, 08 Jun 2011 10:27:01 GMT https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272602#M499098 IAMDV 2011-06-08T10:27:01Z Re: *** Yet another Set Analysis Question *** https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272603#M499099 <HTML><HEAD></HEAD><BODY><P>Hi, DV</P><P>That expression should work... </P><P>Please, check my attachment. </P><P></P><P>Best Regards, </P><P></P><P>Erich</P></BODY></HTML> Wed, 08 Jun 2011 10:32:48 GMT https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272603#M499099 erichshiino 2011-06-08T10:32:48Z Re: *** Yet another Set Analysis Question *** https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272604#M499100 <HTML><HEAD></HEAD><BODY><P>hi Deepak,</P><P></P><P>check what's New in 9.0 Template (Dashboard tab). You will find the solution to your problem. Use the same syntax which they have used.</P><P></P><P>Sorry the copy past is not woking for me.</P></BODY></HTML> Wed, 08 Jun 2011 10:35:24 GMT https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272604#M499100 deepakk 2011-06-08T10:35:24Z Re: *** Yet another Set Analysis Question *** https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272605#M499101 <HTML><HEAD></HEAD><BODY><P>Hi Mate,</P><P></P><P>Not sure i have understood your problem.</P><P></P><P>Have a look at the attached application.</P><P></P><P>-Sridhar</P><P></P><P>EDIT : I`m too late to post this <IMG src="https://community.qlik.com/legacyfs/online/emoticons/sad.png" /></P></BODY></HTML> Wed, 08 Jun 2011 10:37:25 GMT https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272605#M499101 2011-06-08T10:37:25Z *** Yet another Set Analysis Question *** https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272606#M499102 <HTML><HEAD></HEAD><BODY><P> Hope 'FYrNum'&nbsp; is in Number Format and Not Text format..</P></BODY></HTML> Wed, 08 Jun 2011 10:37:38 GMT https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272606#M499102 2011-06-08T10:37:38Z Re: *** Yet another Set Analysis Question *** https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272607#M499103 <HTML><HEAD></HEAD><BODY><P><SPAN style="font-family: trebuchet ms,geneva;">Miguel – You are absolutely right!! Your expression worksand I had unnecessarily made things complicated. I am kicking myself for notthinking about this… its my mistake <IMG src="https://community.qlik.com/legacyfs/online/emoticons/sad.png" /></SPAN></P><P></P><P><SPAN style="font-family: trebuchet ms,geneva;"><BR /></SPAN><SPAN style="font-family: trebuchet ms,geneva;">Dear All – Folks it works and it was my mistake as I amfiltering on different Calendar Year (Names are so similar). Many thanks andsorry for inconvience.</SPAN></P><P></P><P><SPAN style="font-family: trebuchet ms,geneva;">Cheers - DV</SPAN></P></BODY></HTML> Wed, 08 Jun 2011 10:46:55 GMT https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272607#M499103 IAMDV 2011-06-08T10:46:55Z Re: *** Yet another Set Analysis Question *** https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272608#M499104 <HTML><HEAD></HEAD><BODY><P><SPAN style="font-family: trebuchet ms,geneva;">Sridhar, Erika &amp; Bhaskar – Thanks for the suggestions. Iwas filtering on wrong field and spent 2 hours since morning</SPAN> <IMG src="https://community.qlik.com/legacyfs/online/emoticons/sad.png" /> <IMG src="https://community.qlik.com/legacyfs/online/emoticons/sad.png" /></P></BODY></HTML> Wed, 08 Jun 2011 10:51:02 GMT https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272608#M499104 IAMDV 2011-06-08T10:51:02Z Re: *** Yet another Set Analysis Question *** https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272609#M499105 <HTML><HEAD></HEAD><BODY><P>Mate, It happens some time for me also <IMG src="https://community.qlik.com/legacyfs/online/emoticons/cry.png" /> noworries.Cheers <IMG src="https://community.qlik.com/legacyfs/online/emoticons/happy.png" /></P></BODY></HTML> Wed, 08 Jun 2011 12:11:21 GMT https://community.qlik.com/t5/QlikView/Yet-another-Set-Analysis-Question/m-p/272609#M499105 2011-06-08T12:11:21Z