2 Replies Latest reply: Mar 1, 2013 10:06 AM by Michael Solomovich RSS

    How to use Set Analysis to only look at one of the current selections

      I'd like to have all my line chart on a given sheet show daa points related to only one of the users possible current selections.  In this example ProductName.  The user may of course have filtered on other fields such as State, Region, date, etc. but I want to ignore those selctions and only pull points for ProductName.  My dimension on the chart is date.

       

      Within the chart expression I've tried several things but have not yet found the proper one.  Here are some examples of things I've tried

       

      AVG(1<ProductName = ProductName>} SalesAmount)  -- this one from the Set Analysis Qliklearn just trying to obtain the currently selected Product Name and fitler by it, QV shows this syntax as invalid


      AVG({1<ProductName = {$(vProductName)}>} SalesAmount)  -- this one trying to utilize a variable containing the product name populated via a trigger, the variable does appear to be populated when it is shown either on a text object or in the viewer

       

      In all cases I'm using 1 as the identifer so I get all the data, not just the selected data.

       

       

      What am I missing?  Is there a simpler way to selectively limit from the entire set of data based off only one of the users current selections?

       

      Thanks in advance.

        • Re: How to use Set Analysis to only look at one of the current selections
          Stefan Wühl

          If you only want to consider active selections in field ProductName (not also possible values, potentially limited by selections in other fields), try

           

          AVG ({1<ProductName = $::ProductName >} SalesAmount)

           

          using the default state scope operator.

          • Re: How to use Set Analysis to only look at one of the current selections
            Michael Solomovich

            I think your original expression is fine

            AVG({1<ProductName = {$(vProductName)}>} SalesAmount)

             

            What you need is to define correctly the variable $(vProductName).  Try this:

            =chr(39) & concat(distinct ProductName, chr(39) & ',' & chr(39)) & chr(39)

             

            Regards,

            Michael

             

            Correction:

            some product name values can be unavailable because of other selections.  The following logic takes care of it

            =if(GetSelectedCount(ProductName)>0,

                   chr(39) & GetFieldSelections(ProductName,chr(39) & ',' & chr(39)) & chr(39)              // if selected, use selected

                   ,chr(39) & concat({1} distinct ProductName, chr(39) & ',' & chr(39) ) & chr(39)             // if not selected, use all

                   )