2 Replies Latest reply on Jul 2, 2012 7:47 PM by LeeKian Lai

    Calculate Aggregated data

    LeeKian Lai

      I have data (Table 1 in screenshot) as below:

      How can I calculate the total user who have multiple Asset? (Table 2 in screenshot)

      sample workbook attached. Thanks in advance.

      aaaa.jpg

        • 1. Re: Calculate Aggregated data
          Jonathan Drummey

          Hi,

           

          Attached is one way to go about this, using several table calculations. The view is set up with the PCName and username on the Level of Detail shelf so we can aggregate along and across the dimensions:

           

          screenshot1.jpg

           

           

          Both TC PCName and TC User have an Advanced... Compute Using with PCName and username in the right-hand window. This sets the partitioning to be on Office so the results change for each office.

           

           

           

          screenshot2.jpg

           

          Then there are two calculations that are used to generate the third number. # of Assets per User is a table calculation that is then used in the # of Users with Multiple Assets calc. This creates a nested table calculation where each calc has different settings:

           

          Within the # of Users with Multiple Assets calc, the # of Assets per User calc has a nested Compute Using set to Advanced..., with PCName and Office on the right-hand side of the advanced window. This lets the calc find the right total number of assets per user. The # of Users with Multiple assets calc also has an Advanced... Compute Using, with PCName and username on the right-hand side (just like the first two TC calcs).

           

          Cheers,

           

          Jonathan

          • 2. Re: Calculate Aggregated data
            LeeKian Lai

            Thanks very much Jonathan!

            It's very helpful! was struggle on the problem of calculate aggregated data for sometimes.