10 Replies Latest reply: Aug 21, 2012 2:45 AM by Tom Arne Sivertsen RSS

    Calculate average pr month

      Hi,

       

      I want to visualise sales pr customer. One of the dimensions is year/month. To make the data comparable regardless of number of month in selection, I want to visualise the sales expressions as $/month. I've tried with sum(Sales)/count(distinct year_month), which almost gives me what I want. The problem is that if there were no sales to a customer a month, the count excludes that month, which I don't want it to do.

       

      So I want to do someting like:

      sum(Sales)/count(year_month "selected in year_month list box")

       

      Any suggestions how to implement this expression?

       

      Thanks in advance.

       

      Regards,

      Tom Arne Sivertsen

        • Re: Calculate average pr month
          Stefan Wühl

          Try

           

          =sum(Sales) / GetSelectedCount( year_month )

            • Re: Calculate average pr month

              Thanks, it helps a bit, but I see I was a little inaccurate in my description.

               

              GetSelectedCount( year_month ) seems to return "active" selections (those green selections). But if GetSelectedCount( year_month ) = 0 (no date active selections are done), I need to get the count of the full span of dates in the list box (both white and grey).

               

              In addition to that, I see that if I first choose months Jan, Feb & Mar (3 months), and then a customer without sales for e.g. Feb, GetSelectedCount( year_month ) returns 2, but I want 3 (just as the current selections box shows in this example (please see attached screen shot).

               

              Any ideas of further functions to utilize?

               

              Regards,

              Tom Arne

               

              CustomerSales.jpg