I have data like this:
jobid, customer_name, job_completed_date, customer_type, customer_employees , total_sales
1 , walmart , 2025-01-04, groceries, 150 , 140
2 , walmart , 2025-01-06, groceries, 150 , 120
3 , walmart , 2025-02-01, groceries, 150 , 200
4, target , 2025-02-01, retail , 135, 140
5, target , 2025-02-06, retail , 135, 140
each entry is specific to a customer and a specific date , I have calculated fields to sum the sales as per the ask based on condition.
The problem is , I am trying to sum the customer_employees column for each month , since the value is same and repeated , I tried getting the max of each customer and then summed it , which worked .
sum( max({doors_served}, [{customer_name}] ) )
But, now , I am trying to add a filter , for a month , only get the sum of customer_employees for retail category.
, I am trying something like this: but the error is :
Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.
ifelse( IN({customer_type}, [‘retail’, ‘local’]) ,sum( max({customer_employees}, [{customer_name},{job_completed_month}] )) , null ).
I cannot put a filter in the visual as it is an summary visual for each month, I need this value as a separate column in the visual.
job_completed_month is another field that has the year and month of job_completed_date.
Any input is appreciated,Thank you