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")
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).
It seems to me to return the correct result without having to specify all possible dimensions. I'll give the two versions a try when my app evolves forward. Please let me know if you see a simpler way to calculate this without specifying all other dimensions than month.