I am trying to pull up a search that reports on the following:
Average Customer Spending/Sales in the month
The challenges I am facing are numerous, which is ironic - because it seems like such a simple search. First of all, I only want a certain subset of records to pull up - and lines within those transactions. I am looking to filter by the following criteria in a transaction search:
Account
Department
Transaction Type
Date (will remove this when I publish it as a KPI)
What I can do in the results:
Count the Name field
Sum the Amount field
However, I cannot get the Average of the Amount field. It gives me the average per line item on the transactions. I don't want that. I need to be able to say that there were $2,000,000 in sales for 1,000 customers - which comes out to an average of $2,000 per customer.
If I throw the Name field into a formula, and Count({name}) then it gives me the count of lines, not customers. So, I couldn't even Sum({Amount}/Count({name}). . . I also tried to accomplish this from the flip side through a customer search but couldn't succeed.
Does anybody have any ideas for me on this? I want this for KPIs so it has to be a solution that allows for the month/month comparisons, etc.