Set analysis and AGGR()

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Set analysis and AGGR()

sunny_talwar — Mon, 03 Oct 2016 17:24:48 GMT

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])

Re: Set analysis and AGGR()

Anonymous — Mon, 03 Oct 2016 17:36:42 GMT

Thank you, that works perfectly!

topic Re: Set analysis and AGGR() in QlikView

Set analysis and AGGR()

Re: Set analysis and AGGR()

Re: Set analysis and AGGR()