5 Replies Latest reply: Oct 27, 2017 9:56 AM by Simone Trabattoni RSS

    Aggr() and pivot table

    Simone Trabattoni

      Hi community,

      I'm struggling with a pivot table with relative frequencies, and I'm stuck.

      My goals is to achieve a simple pivot table that has:

      rows: workers

      columns: type of sales

      measure: percentage of sales for each type of sales.

       

      Here the data:

       

      mov:

      Load*Inline

      [workerid,sale,type,year

      1,5,a,2016

      1,20,a,2016

      2,1,a,2016

      2,8,a,2017

      3,9,a,2016

      3,2,a,2017

      3,1,a,2017

      4,4,b,2017

      1,5,b,2016

      1,5,b,2016

      2,3,b,2016

      2,5,b,2017

      3,9,b,2016

      3,3,b,2017

      3,9,b,2017

      4,1,b,2017

      ];

       

      Here the result without the percentage but with the simple sum of sales.

       

      one.PNG

       

      So I decided to have the percentage:

      the result should be this: the sum of type a is 46 =25+9+12, and for the type b is 44=10+8+21+5.

      Then:

       

      workerid        type

                         a                   b

      1                 25/46            10/44

      2                 9/46               8/44

      3                 12/46             21/44

      4                    -                  5/44

       

      So I've thought to use the aggr() function in this way:

       

      Sum(sale)/aggr(sum({$<workerid=>} sale),type)

       

       

      The results are correct, but only if you click one of the workerid, if you do not click one, the result misses some data:

      two.PNG

       

      How could I manage to have all the rows filled?

       

      Thanks in advance, attached the app.