Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tshimadajr
Contributor
Contributor

Using columns from different tables and filtering based on a condition

I have the following tables:

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

CustomerProductBacklog
AX40
AX50
AY100

Inventory (single record for each product)

ProductInventory
X30
Y80

WIP (multiple records for the same product)

Lot NumberProductWIP
1111X30
222Y20
333Y20

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

1 Reply
dwforest
Specialist II
Specialist II

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