8 Replies Latest reply: Aug 20, 2013 11:32 AM by Marco Puccetti RSS

    Indirect Set Analysis

    Marco Puccetti

      Hi i'm new to Qlikview and i need a further explaination about p() and e() functions.

      I've read on the guides the meaning of these two functions but i'm really interested in there real functionality.

       

      In this example we can omit the p operator?

       

      sum({$<CompanyName = p({$<CategoryName={'Baby Clothes'}>}

      CompanyName) - p({$<CategoryName={'SportsWear'}>}

      CompanyName)>} Sales)

       

      In particular can you explain me the difference between:

       

      p({$<CategoryName={'Baby Clothes'}>}CompanyName)

       

      and

       

      {$<CategoryName={'Baby Clothes'}>}CompanyName

       

      and you can explain me the same thing form the e function?

       

      Thank you

      Marco

        • Re: Indirect Set Analysis
          Gysbert Wassenaar

          You can't leave out the p operator. This isn't correct syntax: {$<CategoryName={'Baby Clothes'}>}CompanyName. Nor would sum({$<CategoryName={'Baby Clothes'}>}CompanyName) be since names are not something to aggregate. It's not clear what you actually meant. I'll try to explain things a bit below.

           

          Some sample data:

           

          CompanyCategorySales
          ABaby Clothes10
          ASportsWear20
          BBaby Clothes15
          BDresses25
          CSportsWear15
          CDresses20

           

          sum({$<Category={'Baby Clothes'}>}Sales) will return the sum of sales for only Baby Clothes. It will return this per company:

           

          CompanySum of Sales
          A10
          B15

           

          Company C didn't sell any Baby Clothes so it is not shown in the table.

           

           

          sum({$<Company = p({$<Category={'Baby Clothes'}>} Company) >} Sales) will return the total sales for each company that sold (among other things) Baby Clothes. That looks like this per company:

           

          CompanySum of Sales
          A30
          B40

           

          Again company C isn't shown because it didn't sell Baby Clothes so it's not returned by p({$<Category={'Baby Clothes'}>} Company). Instead of the just the sales of the Baby Clothes now the total sales per company is shown.

           

           

          sum({$<Company = p({$<Category={'Baby Clothes'}>} Company) >-<Company = p({$<Category={'SportsWear'}>} Company) >} Sales) will return the total sales for each company that sold Baby Clothes, but also did not sell any SportsWear. The result per company looks like this:

           

          CompanySum of Sales
          B40

          Company A sold Baby Clothes, but also SportWear so it's excluded from the sum. The result is that only the total sales of Company B is shown.

           

           

          Finally e(): sum({$<Company = e({$<Category={'Baby Clothes'}>} Company) >} Sales) will return the total sales of any company that did not sell any Baby Clothes. With the sample data it will return this since only company C didn't sell any Baby Clothes:

           

          CompanySum of Sales
          C35

           

           

          I hope this helps make things clear for you.

          • Re: Indirect Set Analysis
            Marco Puccetti

            Thank you for your answer is very usefull!!

             

             

            Marco

            • Re: Indirect Set Analysis
              Eugeny Ilyin

              OK

              How i can get this table?

               

              CategorySalesComment
              Baby Clothes70Sum Company=A,B
              Dresses75Sum Company=B,C
              SportsWear65Sum Company=A,C

               

              Eugeny

                • Re: Indirect Set Analysis
                  Marco Puccetti

                  Although i think the multiple condition operator is allowed, a simple form could be the following:

                   

                  sum({$<Company={'A'}>}Sales)+sum({$<Company={'B'}>}Sales)

                  sum({$<Company={'B'}>}Sales)+sum({$<Company={'C'}>}Sales)

                  sum({$<Company={'A'}>}Sales)+sum({$<Company={'C'}>}Sales)

                   

                  Somebody knows other form?

                   

                  Marco

                    • Re: Indirect Set Analysis
                      Eugeny Ilyin

                      No,

                       

                      CategorySalesExpressions from text box
                      Baby Clothes70=sum({$<Company = p({$<Category={'Baby Clothes'}>} Company) >} Sales)
                      Dresses75=sum({$<Company = p({$<Category={'Dresses'}>} Company) >} Sales)
                      SportsWear65=sum({$<Company = p({$<Category={'SportsWear}>} Company) >} Sales)

                       

                      I want to use one expression in the table.

                       

                      =sum({$<Company = p({$<Category=...?...>} Company) >} Sales)