2 Replies Latest reply: May 25, 2013 5:51 AM by Anton Bezd RSS

    different aggregations

    Anton Bezd

      Hi all!

      I have sales details table with products and a quantity of sales points where these products have been sold. I would like to calculate the percentage of sold products in total sales of shops, where these products have been sold. i.e.

      I have a product A and it have been sold in 5 shops of 20. Sales sum of product A = 10, total sales of 5 shops (B) is 1 000. I want to do 10/1 000 and show the result in table with columns: products; sales of products, total sales of my selected 5 shops, percentage;

       

      Thanks!

        • Re: different aggregations
          Gysbert Wassenaar

          Not quite I understand you correctly. But try creating a straight table with product as dimension and three expressions:

          Sales of selected shops: sum(Sales)

          Sales all shops: sum({<ShopID= >} Sales)

          Percentage: Column(1)/Column(2)

            • Re: different aggregations
              Anton Bezd

              Sales all shops: sum({<ShopID= >} Sales) - i dont need sales of all shops. I need total sales of shops, where this is product was sold, for example, in 20 shops have been sold many products, but product A have been sold only in 5 shops of 20, and I need total sales of these 5 shops.

               

              I'm willing to give more explanation if needed.

               

              in their attempts to solve this problem I settled on two non-working solutions

              1. sum({<[product]=, shop = {"=sum({<shop=>} sales)<>0"}>} total [sales])

              2. sum({<[product]=, shop = p({<[product]= p({$}[product])>})>} [sales])