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

    Calculate average pr month



      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.



      Tom Arne Sivertsen

        • Re: Calculate average pr month
          Stefan Wühl



          =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?



              Tom Arne