Calculating totals from 2 different aggregates

Calculating totals from 2 different aggregates

  

I am trying to calculate a total for products available.

Entity "Equipments" contains products that were installed, entity "Purchase Order Products" contains products and quantities that were ordered.

I've tried combining both in an aggregate in the "Inventory" screen preparation but can't find a way for it to add correctly. OML attached. Any advice would be highly appreciated!

Hi Elmar,

I'm not quite sure what you're trying to achieve. What does "Equipment" entail exactly, and how does it relate to "PurchaseOrderProducts"? What is the "total for products available"? When is a product "available"?

Secondly, you really should name your Attributes in a consistent way. There's "Prodid" (no capital "I", Entity name abbreviated) vs. "ProductId", there's EqId (lower case "d", not an actual Entity Id) vs. CustomerID (capital "D"), there's "PoProductQty" (two abbreviations) vs. "PurchaseOrderId", and so on. This is mightily confusing for anyone that has to maintain this code.

Hi Killian, and thank you for your quick response!

The EQUIPMENT entity stores records of products that were installed at a customers residence.

The PurchaseOrderProducts entity stores records of products that were ordered.

The goal is to calculate a current count of products in stock to avoid over-ordering products when not necessary.

I would like to track how many of each "Products" I have available. 

Available Products = Ordered Products - Installed Products

Available = sum(equipment.Quantity) - sum(PurchaseOrderProducts.PoProductQty) for each individual Product.


I apologize for name inconsistencies! I will certainly fix those!


Thanks, Elmar


Solution

Hi Elmar,

I think where I got confused is that I assume "ordered" means "by the customer" as opposed to "by the company".

It should be something like this?

So a join on the ProductId, then add a Sum on PruchaseOrderProducts.PoProductQtySum and Equipment.Quantity, and add a new column to calculate the difference, like this:



Solution