2 Replies Latest reply on Sep 20, 2012 8:29 PM by Carl Johan Ragnarsson

    Sum of average

    Carl Johan Ragnarsson

      I have data like this:

       

      date  item sold nusers

      d1     i1     5     1

      d1     i2     3     1

      d1     i3     1     1

      d2     i1     2     2

      d2     i4     4     2

       

      Here d1, d2... are dates,  i1, i2... are names of items, sold is the sales for items of the particular type, and nusers is the number of users of the product on that day. By definition, nusers is the same for all the rows for a particular date.

       

      I would like to compute the average items sold per user over a time period: i.e. (5+3+1 + 2+4) / (1 + 2) = 5

       

      Or, in another word, I'd like to be able to first average the nusers column for each date, and then sum up over the different dates.

       

      If I only have data for one day, I could get the average items sold per day as SUM(sold)/AVG(nusers), or equivalently in this case, SUM(sold)*SUM(nusers)/COUNTD(item)/COUNTD(date).

       

      However, with data for several days, where the items sold may change from day to day, this computation will break. Is there a better way to create such a computation (or to alter my input data) without padding the data with all items in the database for each day of data?

        • 1. Re: Sum of average
          Jonathan Drummey

          Hi Carl,

           

          I've attached a workbook that I think meets your requirements. The way I interpreted them is that you want a sum of all the items sold, divided by the sum of the count of users per day. Since the count of users per day shows up on every record, we want to only count those users once. A way to do that is to bring the Date into the view on the Level of Detail Shelf, then use a table calculation to sum the count of users per day. However, since Date is now in the Level of Detail and would return multiple values per cell in the view, the other calculations in the view need to be modified to remove that.

           

          You can filter the dates and/or the items and the calculations will appropriately adjust.

           

          Cheers,

           

          Jonathan

          • 2. Re: Sum of average
            Carl Johan Ragnarsson

            Jonathan,

             

            Thanks for the quick response.  It sounds like you understood my question correctly, but unfortunately, I am not able to open your workbook (it says it is missing an underlying Excel file).

             

            I might be able to reproduce it myself, but I don't quite understand what the "Level of detail" shelf does, so I hope you can upload your worksheet again.

             

            Thanks,

            Carl