Solved: Conditional location slice - Page 2

Robblaze · 09-20-2021 07:53 PM

Hoping someone can help, I’m having trouble wrapping my head around how to filter this slice.
I have multiple columns, relevant are [UPC], and [Location]. All items in the database have their location set as either Sales Floor, Sold, or Bin A (Bin B, Bin C, etc,) Regardless of Bin location, they’re all considered overstock. There can be any number of duplicate UPC’s, with a maximum of 1 of them labeled as Sales Floor, and the others divided between Sold and the various overstock locations.
I have a slice that only shows Sales Floor items, another that shows Sold, and another that shows overstock, those were easy.
What I need next is a restock slice, that lists all UPC’s in overstock, that don’t also appear in Sales Floor. Basically, the overstock slice minus the sales floor slice. Ideally, it would only generate a single instance, if there were multiple overstock locations. Random among these is fine, as long as its just 1.
Example: UPC 1234 has 6 rows, 1 is Sales Floor, 1 is Sold, and 4 are overstock. Slice doesn’t include this. UPC 4567 has 5 rows, 1 is Sold, 4 are overstock. Slice does include this, but only shows 1 of them from overstock, not all 4.
I believe I need to use a combination of Not() and In() applied to a list of [location]=“Sales Floor”, but so far I’m hitting a wall.
Hoping someone has an idea, this feels like it should be simple but I can’t wrap my head around it.

Marc_Dillon

Here is what I think I would do:

First create a “potential restock” slice. Basically, any UPC that is in overstock, where the same UPC is NOT in sales floor.

AND(
  CONTAINS( [location] , "Bin" ) ,
  NOT( IN( [UPC] , Sales_Floor_Slice[UPC] ) )
)

Second, create your actual “restock” slice, that just picks one record from the potentially multiple of the same UPC in the first slice.

[_THISROW]
=
MINROW( 
  "potential_restock" , 
  "_RowNumber" , 
  [UPC] = [_THISROW].[UPC] 
)

Now, I only thought about this for 5 minutes or so, so I’m not guaranteeing it is the best way, but I think it should work. Feel free to replace MINROW with MAXROW, or just do an ANY(SELECT(…)).

View solution in original post