I’m trying to put together a custom report which would be super useful for brew schedule planning, but can’t figure out how to pull the right data out in a useful format.
What I’m after is a summary of units sold by product, ideally split down by week over a couple of months, and along side that, a column reporting my current available stock level for each product. Essentially a report which allows me to, at a glance, measure up how much we have in stock against the rate at which we move through it, to help setting production priorities.
I’m very happy to work through building it myself if someone can give me a bit of a hand with the BrewwQL code needed to get there - it feels like it’s a bit of a mashup of the existing ‘Total Sales by Product’ but split down by several periods, and ‘Products available for Sale’ reports.
Welcome to the Breww Community!
If you take a look at the Stock allocation report, which can be found at Products > Tools > Allocation report, you’ll see a summary of stock levels by product, split by week or day, along with the current stock level. Would this work? If not, let me know and I’ll see if we can add something to show the number sold instead of stock levels.
Thanks for this - I’ve just had a quick look, but not sure if I’m missing something on how it ought to be working? The screenshot below shows what I’m seeing when I run that report for the last month, which appears to show my current stock but very little variation in any of the other lines despite us having moved through plenty in the weeks specified. It also seems to only include lines which have currently open orders, which limits how useful it’d be for stock and production management. For instance, if I run it for 1st-30th June (last calendar month) it returns no data because there are no open orders with allocations.
I think something which shows the number sold would be much more useful - that way I can see (based on past volumes) what I might expect to be moving through in the next ~x weeks, what I’ve currently got in stock (and potentially also in planned rackings?) and plan the production accordingly.
Ah yes, that report may not be what you need after all. That report looks at the number of deliveries scheduled and number planned to be packaged that week to predict stock levels. We are planning on adding forecasting based on previous products sold as well, would that help you?
Alternatively, you may find the Aggregate report - Products sold (order lines) helpful. If you set the data grouping to ‘Week’, the first metric to ‘Total item quantity’ and the First metric extra grouping to ‘Product’, that may be what you’re looking for?
Please let me know if not, and we’ll sort something!
Thanks again for coming back on this and sorry for the slow reply! The Products sold (order lines) report is really helpful and gets me most of the way there, particularly if I download the raw data so I can pull an average weekly unit sales figure for each product line. Would there be any way of displaying an average on the chart for the time period specified to save downloading and calculating that manually?
What I’m ideally looking for is an ability to calculate stock coverage - i.e. juxtapose that average unit sales figure against what I’ve currently got in stock to get a rough idea of how long it will last me at current sales rates.
As it stands, I can get all of that data to figure that out - stock levels from ‘All Products List’ under the Products tab, and Average unit sales from the above report, but if there’s any way to get them in the same place so I don’t have to do all the intermediate steps it’d be amazing. Equally if I’m the only person who thinks this would be useful and it’s a big job for you guys, don’t sweat it too hard!
