Set analysis and AGGR()

[Anonymous]

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.

[sunny_talwar · Accepted solution]

May be this:

Sum({$<[LinkId]={"OPSJ"}, [Job Completed Date] = {"<=$(=date(max(Date)))>=$(=date(min(Date)))"}, JobCode = {"=Sum([JobLine Inventory Quantity]) = 0"}>} [Cost Transaction Revenue Actual])

[Anonymous]

Thank you, that works perfectly!