Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Thorqqque
Contributor
Contributor

Use Filter to get Non-Filtered Accompanied "Shipped With" data

Using 4 lists to filter: Product Code, Item, Order Number, Order Line:

My client needs to have a "Shipped With" straight table (or other grid/list sheet object) showing all distinct accompanied items and SUM(quantities) that were ordered with the same order(s) as anything in the filter.  Example cases:

Case #1: If I filter on one order and one order line, the "shipped with" table should show every item (and summed qty) shipped with one order. 

Case #2: If I filter on an item, the grid should show all distinct items on all orders that include that filtered item.

Case #3: if I filter on any one (or more) product code(s), the straight table should show all distinct products  (and summed qty) that were included on (shipped with) any order that also included any item with the filtered product code(s).

I've been using Qlik for quite some time, and I've been able to accomplish nearly everything I've needed, but this one has me stumped.

It seems if I put "Item" as a dimension, I only get items that are part of the filter; never the included (shipped with) items.

My attempts at using Set Analysis as a Dimension of the straight-table, to get around the filter, show up as errors.

I've been searching for a couple of weeks, hoping I could find someone doing a similar thing, maybe with dates, that I could alter to fit my purpose, but to no avail.

My current attempts are to flag orders that match the filter, using GetSelectedCount() and use that as a dimension, but so far I'm just getting errors.  I'll keep working on it.

If you have an idea I might try, or an example of something that does anything similar, I would be grateful for any suggestions.

 

Labels (3)
1 Reply
edwin
Master II
Master II

thats a lot of ask.  but if this is acceptable, it might help point you to how to solve you other problems. 

first, selecting an order and an item and requiring all other orders with the same item:

1. create a variable that will hold the selected item:  vItem=only(Item)

2. in your expression, add the all possible values function -> all possible orders with this item:  =sum({<item=,Order=p({<Order=,item={'$(vItem)'}>}Order)>}ShipQty)
it means select all Orders with item = selected item, then sum the shipped qty
even if a single order is selected (filtered) the condition Order= will override it and return all orders that satisfy the p() function.