0

Items stock per every Friday

Tomasz

January 09, 2018 15:03

Answered

Follow

Hello,

I would lkie to create report with stock of the item for every Friday. Can somebody help me with that?

I want to add filter for a month, and as a results I want to have stock per every item/every friday.

Table: Item Ledger Entry,

 

 

• Facebook
• Twitter
• LinkedIn

2 comments

• 1
  
  Harry Lewis January 10, 2018 18:02

  You would probably want to first figure out how many Fridays are in the month.  Assuming that the start date for the month is in a cell named "start_date", you could do that with this function:

  =4+(WEEKDAY(DATE(YEAR(start_date),MONTH(start_date)+1,0),2)>=5)

  Next, assuming that value is in a cell named "num_fridays", you would want an NL(Rows) function to create that space for each Friday:

  =NL("Rows=2",NP("Integers",1,num_fridays))

  You would then want to compute the date for each Friday.  Assuming that the NL(Rows) function is in cell H8:

  =CEILING(EOMONTH(start_date,-1)-5,7)+(H8*7)-1

  Since the above NL(Rows) includes two rows (8 and 9): In row 9, you could place your function for retrieving your item numbers and the function or formula you want to use to calculate the current stock value for each item.

   

  

  Edited by Harry Lewis January 10, 2018 18:06
• 0
  
  Tomasz January 15, 2018 13:03

  Thanks, it woks.