3 Replies Latest reply on Nov 29, 2012 1:08 PM by Alex Kerin

    Average Sales Calculation

    Xiaodong Han

      I need to report average sales on a big data file.  The business quesion is simple, what's the average sales for product A.  I can do a simple average calculation and the result is there.  However, for the stores with 0 sales, the data file has no entry for it.  Basically, the average is based on store "Selling" Product A.  Not a system side average.  We cannot go back to data file to add 0s, Hope someone can help.  There must be a way to do it in formula.

       

      I attached a sample file.

       

      Thanks,

       

      Xiaodong

        • 1. Re: Average Sales Calculation
          Alex Kerin

          I don't think  your example data set is actually missing any data, but this should work: avg(zn([Sales]))

           

          zn changes a null to zero

          • 2. Re: Average Sales Calculation
            Xiaodong Han

            Alex, in my example, for 1/1/2012 data, there is one store didn't have any sales for product B, in that case, the store count is 4, instead of 5.  We need total/5, not total/4.  In the raw data, there is no 0, no NULL for product B for this store.  The formula  avg(zn([Sales])) returns same result.

             

            Thanks,

             

            Xiaodong

            • 3. Re: Average Sales Calculation
              Alex Kerin

              Got it - no entry at all.

               

              In this case we can count the total distinct stores. To do this we need to extract the data as count distinct is not available otherwise. Then we can find the max value across all of the partitions:

               

              window_max(countd([Store]))

               

              We can divide sales by this.

               

              If you didn't want to extract, but were certain that at least one product or date had every store and that there were not multiple same store entries for that date or product, you could use count. Countd is much safer.