Sum if total of other contition is 0

[MarcB83]

Hi,

I am trying to get the sum of inventory for all products  for which the total sales is 0 in all regions. So in the below example Product B should be completely excluded from the calculation as this has sales for region B. 

Product	Region	Inventory	Sales
A	A	0	0
A	B	1	0
A	C	5	0
B	A	5	0
B	B	5	2
B	C	7	0
D	A	9	0
D	B	4	0
D	C	1	0

[qv_testing]

Try this

Data:
LOAD * INLINE [
Product, Region, Inventory, Sales
A, A, 0, 0
A, B, 1, 0
A, C, 5, 0
B, A, 5, 0
B, B, 5, 2
B, C, 7, 0
D, A, 9, 0
D, B, 4, 0
D, C, 1, 0
];

Inner join
Load * where Total=0;
load Product, Sum(Sales) as Total
Resident Data
group by Product;

[anat]

Data:
load distinct Product where Sales>0;

LOAD * INLINE [
Product, Region, Inventory, Sales
A, A, 0, 0
A, B, 1, 0
A, C, 5, 0
B, A, 5, 0
B, B, 5, 2
B, C, 7, 0
D, A, 9, 0
D, B, 4, 0
D, C, 1, 0
];

noconcatenate

Table:

Data:
LOAD * INLINE [
Product, Region, Inventory, Sales
A, A, 0, 0
A, B, 1, 0
A, C, 5, 0
B, A, 5, 0
B, B, 5, 2
B, C, 7, 0
D, A, 9, 0
D, B, 4, 0
D, C, 1, 0
] where not exists(Product);

drop table Data;

[MarcB83]

Thank you, I understand this is done in the load script. Is there also a way to do this in the set analysis?

Much appreciated.

[marksouzacosta]

Yes, however this will be easier if you do some manipulation in your Load Script.

We can use a variation of the code share by @qv_testing 

Data:
LOAD * INLINE [
Product, Region, Inventory, Sales
A, A, 0, 0
A, B, 1, 0
A, C, 5, 0
B, A, 5, 0
B, B, 5, 2
B, C, 7, 0
D, A, 9, 0
D, B, 4, 0
D, C, 1, 0
];

LOAD
Product,
Sum(Sales) as Total
Resident Data
group by Product;

Now, in your Expression you can do the following:

Sum({<Total = {0}>} Sales)

[anat]

you can try with E function in set analysis like below

sum({1<product=E({$<sales={0}>}product)>}sales)

[Chanty4u]

Try this

Sum({<Product = {"=Sum(Sales) = 0"}>} Inventory)