Hi,
Any help would be greatly received as this issue is driving me mad!!
I have 2 queries, one showing a list of customers that have ‘Opportunities’ and another showing ‘Sales’. I have merged on customer number so that I can only show sales for those particular customers that have an opportunity.
The customer’s with opportunities have a Revenue Start Date and I need to calculate the sales for each customer but only from after this particular date. I have managed to get this bit to work in a Cross table showing billing date along the top and customer number down the left using the following formula:
Net Value Since Revenue Start Date =If([Billing doc. date]>=[First Date];Sum([Net Value Bef Chgs]);0)
Any date that is prior to it’s ‘Revenue Start Date’ (First Date) is 0 and any after have the correct value in it.
Now the problem is when I want to show this Net Value Since Revenue Start Date by month rather than by the billing date.
I have tried numerous formulas, one being this:
=Sum([Net Value Since Revenue Start Date]) ForEach ([Billing Date (MMM-YY)])
but they all end in #MULTIVALUE and I’m not sure where I am going wrong. It feels like it should be an easy thing to calculate.