Summarize over dimensions and expression in a pivot without duplicates

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

I have a large database of items in my ERP system with inventory on different levels and warehouses. I would like to summarize the total of the variant and warehouse for each bom_item. Then deciding if this should be scrapped or trigger a sellout process based on the total value.

The result I would like to achieve is as the red in the enclosed picture, but how can I summarize all the numbers in yellow and all the numbers in blue separately. It would be best if I can show these calculations as a calculated dimension so I can show the total and the flag in the left of the pivot.

In the example I have:

two supplying warehouses (bom_warehouse)
two local warehouses
two base items (bom_item) used to create the finale items
three finale items created from the bom_item
inventory value both for the bom_item and the finale item

topic Summarize over dimensions and expression in a pivot without duplicates in QlikView

Summarize over dimensions and expression in a pivot without duplicates