Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Summary:
Thanks to swuehl for helpful contribution!
- Tom Arne
Try
=sum(Sales) / GetSelectedCount( year_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
Maybe like this
=sum(Sales) / count({<Customer>} distinct Month)
You'll need to add all fields that might limit the Month selections in the set expression, comma separated, e.g. with an additional Product:
=sum(Sales) / count({<Customer, Product>} distinct Month)
Using "=sum(Sales) / count({<Customer>} distinct Month)",
it still returns 2 instead of 3 in my test app (with only month and customer as dimensions). Can I upload my example somewhere for you to have a look in what I mean?
-Tom Arne
Ah, sure, I forgot that Customer is / could be a dimension.
Upload is available from the advanced editor (link in editor in upper right corner, should be default when editing an existing post).
edit: and try
=sum(Sales) / count({<Customer, Product>} total distinct Month)
I have uploaded now ☺
Thank you! Now I get the correct numbers.
But do I need to specify the dimension(s)?
=sum(Sales) / count({1} total distinct Month)
seems also to return my expected number as well (and should be more robust...). Do you see any drawback by specifying {1} instead of the dimensions?
Regards,
Tom Arne
Well, it depends on your needs, {1} could be too robust. What if you select only Jan and Feb (two months)?
Do you then want your denominator to return 2 or 3 in your sample?
I see your point. I want 2 in that example, so {1} will be wrong.
I played a little around and came up with this messy version utilizing the function GetFieldSelections together with count {1} total:
=
if(IsNull(GetFieldSelections(Month)),count({1} total distinct Month),(substringcount(GetFieldSelections(Month,';'),';')+1))
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.
Many thanks!
-Tom Arne