4 Replies Latest reply: Jan 9, 2012 10:49 AM by user5674 RSS

    count in sub aggr in pivot chart on total selection

      Hello,

       

      I have  this type of data:

      ProductMainIDSubID
      A112
      A14
      A14
      A213
      A34
      B112
      C25
      C17
      C19

       

      I have a pivot with Product dimension and a calculated dimension using some rank function which give a list of SubID.

       

      I want an expression for each line of my chart to display the count of distinct SubID in the MainID associated to the SubID of the line.

      as below:

      ProductRanked SubIDCount Sub ID
      A44
      124
      132
      B124
      C52
      C74
      C94

       

      for example in total selected data i have 4 Sub ID in the MainID "1", so for the line Product "A" and Sub ID "4" I have associated MainID "1" and I want display the total  count of SubID for this MainID. Here 4.

       

      I guess it's possible using aggr, total and set analysis, but I can't figure the good expression.

       

      Could you help me?

       

      Thanks.

        • Re: count in sub aggr in pivot chart on total selection
          Stefan Wühl

          Maybe like attached?

           

          Regards,

          Stefan

            • count in sub aggr in pivot chart on total selection

              It solve it.

               

              Thanks you very much.

               

              Just for my understanding, could you explain how it work?

               

              The Total<MainID> mean take all MainID in selection not just MainID of the row or dimension level.

              If i correctly understand it:

              1. It count distinct SubID by Product and MainID (aggr(X,Product,mainID)).
              2. For all selected MainID (Total<MainID>).
              3. Then the pivot dimension restrict result only on current row (->only one MainID).

               

               

               

              Or it's the aggr() dimensions which restrict only row level count to current MainID in aggregating the global count by all selected MainID?

               

              And why the Max function?

               

              Regards,

               

              Sylvain

                • Re: count in sub aggr in pivot chart on total selection
                  Stefan Wühl

                  Sylvain,

                   

                  I will try to explain:

                   

                  The aggr() function could be considered as a table within the expression, so maybe let's start with creating a separate chart object (e.g. a pivot table) with dimensions product, MainID and SubID and expression = count(total<MainID> distinct SubID)

                   

                  The Total<MainID> mean take all MainID in selection not just MainID of the row or dimension level.

                   

                  No, that's not correct, from the Help:

                  The total qualifier may be followed by a list of one or more field names within angle brackets. These field names should be a subset of the chart dimensions. In this case the calculation will be made disregarding all chart dimensions except those listed, i.e. one value will be returned for each combination of field values in the listed dimension fields.

                   

                  So, it's the other way round: Group by MainID and aggregate over Product and SubID.

                   

                  This inline expression table we created is now restricted to the row values of Product and SubID, thus for Product = A and SubID=12 we get 4, for Product=A and SubID=13 we get 2.

                   

                  The combination of Product=A and SubID=4 is however ambiguous, this combination shows to MainID: 1 and 3.

                   

                  So we get two values for the distinct count per MainID: 1 and 4.

                  I decided to choose the max value to match your sample attached to your OP, but this might be an assumption that is not correct, so you could also sum the single results for combination using sum() instead of max(), or show both values using concat().

                   

                  Hope this explains my approach better,

                  Stefan