9 Replies Latest reply: May 19, 2015 3:09 PM by Mike Grattan RSS

    Set analysis question

    Mike Grattan

      Hi; I'm a newbie trying out the free versions of Qlik View and

      Sense.  I'd like to see if there's a way to do a particular type of Set Analysis. 


      Assuming my current selection includes products of the type PT and a particular customer and channel.  How would I compare total Sales for that selection against total sales for the same customer, same channel, but NON PT products? 

        • Re: Set analysis question
          Greeshma K



          You can write two expressions as shown below.

          1. sum(Sales)


          2. sum({<Product=>}Sales)


          The first one will give you the Sales for all selections that you have made whereas scond one will not consider the selection on Product Type but it will give the sales for slected customer and channel.




          • Re: Set analysis question
            Jonathan Dienst

            Sales (respecting all selections)



            Sales ignoring customer and channel selections and only products not selected

            Sum({<Customer=, Channel=, Product=E(Product)>} Sales)

            • Re: Set analysis question
              jagan mohan rao appala



              Try like this


              For Current Selections:



              Total Sales for that selection against total sales for the same customer, same channel, but NON PT products?

              Sum({<Product=E(Product)>} Sales)




              Sum({<Product={'Non PT Product1', 'Non PT Product2', 'Non PT Product3'.........'Non PT Productn'}>} Sales)

              Sum({<Product={'NON PT'}>} Sales)




              • Re: Set analysis question

                Assuming you already applied selections in Customer, Channel and Product ("PT") dimensions:


                1- Sales for the selected values:

                     Sum (Sales)


                2- Sales for the same Customer(s), same Channel(s) but for non PT Products (excluded values because "PT" is selected):

                     Sum({<Customer=P(Customer), Channel=P(Channel), Product=E(Product)>} Sales)

                Where P() will give you the Possible values of that field in the current state of selections and E() will give you the excluded values (the values in grey for the current selections).

                • Re: Set analysis question
                  Yevgeniy Popov



                  You can write two expressions:


                  sum({<Product={'NON PT'}>}Sales)



                  • Re: Set analysis question
                    Mike Grattan

                    Hi folks; thanks for the great feedback!


                    I tried Borja's recommendation and that seems to work for doing a straight sum.  I've created a Master item with this formula and another one for Volume (shipped units).  If I want to get an average, can I add another Master item measure for a calculation like Volume/Sales?  I tried it and the result is just a null value.