Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
tshimadajr
New 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
Valued Contributor

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

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

Community Browser