Using columns from different tables and filtering based on a condition

tshimadajr — Thu, 02 Jun 2016 19:01:26 GMT

I have the following tables:

Backlog (can have multiple records for the same customer and product)

Inventory (single record for each product)

Product	Inventory
X	30
Y	80

WIP (multiple records for the same product)

I would like to find those products at risk where Backlog > (Inventory + WIP). In this example, for each product the calculation is:

Product X: Backlog of 90 > (Inventory of 30 + WIP of 30) --> meets the above condition, the product is at risk

Product Y: Backlog of 100 < (Inventory of 80 + WIP of 40) --> does not meet the above condition

In this case, I would like to show in chart/table only product X that matches the condition.

Today I have a table with dimension Product and a Difference measure where:

Difference = Sum(Backlog) - Sum(Inventory) - Sum(WIP)

I take this to Excel and eliminate records where Difference is lower than or equal to 0.

Is there any way in Qlik Sense to show in the table only the products that match the condition?

Regards

Re: Using columns from different tables and filtering based on a condition

dwforest — Wed, 20 Jul 2016 15:59:04 GMT

Add a flag dimension, BacklogGreaterInventory = If(Sum(Backlog) - Sum(Inventory) - Sum(WIP)) >0,1,0), then filter on that.