0

Average period of storage

Jet Reports Historic Posts

November 15, 2012 08:52

None

Follow

Hi,

we want to calculate the average period of storage for our items.

Basic info:
- All items are being tracked
- We are producing aroung 25-30k a month

What I did:
- Get a list of all items that were produced in a certain time frame
- filters: posting date, item no., lot no.
- Get a list of all item ledger entries with Lot.-No., posting date of "output", posting date of "sale"
- Lines without sale are being calculated with today's date
- calculate the difference between the date of output and sale for every single line
- add these up and calculate the average

Just looking at a single item or a time frame of 1 week is working quite good. But if I want to have a look at a full month the list simply explodes as I am generating about 25k lines. Looking at a full year would be totally impossible.

Unfortunately Jet Reports does not provide the possibility to sum the posting dates. My idea was something like: (nl("sum";"item ledger entry";"posting date";"entry type";"sale";additional filters) - nl("sum";"item ledger entry";"posting date";"entry type";"output";additional filters)) / nl("count";"item ledger entry";"posting date";"entry type";"sale";additional filters)

Hope it is clear what I want to do and that all my translations are correct. Anybody an idea?

Thanks
Andreas

• Facebook
• Twitter
• LinkedIn

3 comments

• 
  Jet Reports Historic Posts November 15, 2012 14:50 Official comment

  Hi Andreas,
  
  So we could make your NL(Sum) functions work using a calculated field like this:
  
  =nl("sum";"item ledger entry";"=VALUE(NF(;""posting date""))";"entry type";"sale";additional filters) - nl("sum";"item ledger entry";"=VALUE(NF(;""Posting Date""))";"entry type";"output";additional filters)) / nl("count";"item ledger entry";"posting date";"entry type";"sale";additional filters)
  You may need to change the VALUE function in the calculated field to the equivalent in your local language if you are using non-English Excel although I'm not positive about this. If you are trying to count unique dates in that last function, you should change it to a CountUnique. Otherwise, you should get rid of the Field parameter (Posting Date) since that is not used in a Count function.
  
  I should warn you that this could be extremely slow. Jet has to go back to Excel and have it execute the VALUE function for each record that it is summing so this could easily increase your report runtime by a large factor. However, if you really want to see the sums without listing out all the records, this should work. Does this help?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts November 15, 2012 15:01

  Hi Hughes,
  
  thanks for the reply. I am using the german version. So I might need to translate it. But I will try it and come back as soon as I have results. Let's see how slow it will be. My current list with the 25k lines took about 4 hours.
  
  Regards
  Andreas

• 0
  
  Jet Reports Historic Posts November 20, 2012 10:41

  Hi Hughes,
  
  you're a star!
  
  Needed to set up a few additional filters, but it works really well. Got down to 440 rows and 15 minutes runtime.
  
  Thanks a lot
  Andreas