0

Report shows Top # of Items within each Location, for a specific time period

Karen Atkinson

September 05, 2019 14:10

None

Follow

I am running an Inventory efficiency report.

This report currently lists all locations (rows) and Total Inventory Value based on dates (columns are 0-4 m / 4-6 m / 6-9 m /12-24 m etc)

My Total Inventory Value is pulling from the Item Ledger Entries, and filtered using Remaining Qty.  This report works, and well.

The addition to this report that is begin requested is:

Can we see the Top 10 items in each section?

ie. within each Location, and within each time category, what are the Top 10 Items that have the most inventory value still remaining ...

I have set up the Replicating Rows function for the Locations, and an example of the Inventory Value currently being pulled is:

=NL("Sum","Item Ledger Entry","=NF(,""Remaining Quantity"") * (NF(,""Cost Amount (Actual)"") + NF(,""Cost Amount (Expected)"")) / NF(,""Quantity"")","Depart Dimension",$C41,"Posting Date",VLOOKUP(J$5,Dates,2,FALSE),"Remaining Quantity","<>0")

How to I add Top 10 Items to each Location (actually using Depart Dimensions)?

 

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  Bryan Robinson September 05, 2019 18:18

  You wouldn't really be able to show the top 10 with one replicator across multiple columns.  What if the top ten for the 0-4m is totally different than the 4-6m? You need a distinct replicator for each list to return the top 10 for that criteria.

  Easiest way is make this into separate replicated sections stacked on top of each other for each of your columns. 

  

   

  There are creative ways to put them side by side using NP(Integers), NL("Filter") and NL("1"...),NL("2"...), etc. but is a bit more advanced and you would still need a separate replicator for each column you want to sort on.  Might need to hit up a Jet consultant to implement that solution.