I need to get the summary of the revenue ONLY for jobs that have a total inventory of 0. If any of the lines have inventory > 0, then none of the revenue from that job should be included in the sum. In this example, the sum would be 4500.
Example Data:
Customer JobCode JobLine JobLine Inventory Quantity Cost Transaction Revenue Actual
Cust1 123456 1 1000 0
Cust1 123456 2 3000 50
Cust1 123456 3 0 10000
Cust2 123457 1 0 2000
Cust2 123457 2 0 1500
Cust3 123458 1 1000 100
Cust3 123458 2 2000 0
Cust2 123459 1 0 1000
Currently I have this as my expression for Revenue:
If (Sum([JobLine Inventory Quantity]) = 0,
sum({$<
[LinkId]={"OPSJ"},
[Job Completed Date] = {"<=$(=date(max(Date)))>=$(=date(min(Date)))"}
>}
[Cost Transaction Revenue Actual]),
0)
Which sort of works because I have JobCode as one of the dimensions in my chart (See attachment for screenshot of chart).
However, I need this chart to have customer as the only dimension. If I remove the JobCode as a dimension from this chart, then I will get 0 for the Revenue for any customers that have inventory.
(Also notice that the total revenue showing at the top is 0 because the if statement is false when considering all joblines. This is another problem.)
The AGGR() function looks promising, but I could not get it to work with my set analysis.
I appreciate any help! Let me know if there is additional information I can provide.
