I'm having a problem trying to show the data the way I need to.
I have a data source of customers. I need to calculate how many employees are needed to service those customers. Call this field EMPLOYEES NEEDED. The data source contains a field for customer level (CUSTOMER LEVEL) but does not have the calculation for 'employee allocation'. So I've created a field (EMPLOYEE ALLOCATION) to give me that allocation.
I start to have problems because my data set has multiple records for each customer. So to calculate the number of customers, I created a field called NUMBER OF CUSTOMERS which is just a COUNTD(Customer #).
The EMPLOYEES NEEDED is a simple calculation: EMPLOYEES NEEDED = NUMBER OF CUSTOMERS * EMPLOYEE ALLOCATION
I have to wrap the EMPLOYEE ALLOCATION in an ATTR() for this to work.
This causes several issues with my output:
- I cannot get a grand total on my EMPLOYEES NEEDED
- If I don't include either CUSTOMER LEVEL or EMPLOYEE ALLOCATION in the sheet, the EMPLOYEES NEEDED returns nothing.
- If I try to use EMPLOYEES NEEDED in a calculation, I keep getting the 'Cannot mix aggregate and non-aggregate data' error.
I'm fairly certain that I need to either change the way I'm calculating something or use a LOD to accomplish this, but nothing I've tried works.
Any help is much appreciated!
Lee,
Try changing the following calculations:
Employee Allocation
{ FIXED [Customer Level]: AVG(
CASE [Customer Level]
WHEN "Level 1" THEN .5
WHEN "Level 2" THEN .2
WHEN "Level 3" THEN .075
WHEN "Level 4" THEN .03
END
)}
and
Number of Customers
{ FIXED [Customer Level]: COUNTD([Customer #])}
Then in Employees Needed, you can remove the ATTR:
Employees Needed
([Number of Customers]) * ([Employee Allocation])
That should fix the issue and the table should look like the following:
Attached is the workbook. Please let me know if you have any questions.
Thanks,
Kevin Flerlage
