1 Reply Latest reply: Jul 20, 2016 11:59 AM by David Forest RSS

    Using columns from different tables and filtering based on a condition

    Thomas Shimada Jr

      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