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")
sum(Sales) / count({<Customer, Product>} total distinct Month)
remember to include all dimensions except Month inside <>.
sum(Sales) / if(IsNull(GetFieldSelections(Month)),count({1} total distinct Month),(substringcount(GetFieldSelections(Month,';'),';')+1))
gives the correct result without having to specify all other dimensions. But is rather messy. Any suggestions for a simple expressions without specifying all other dimensions is appreciated.
