I have a task to make a "Zero stock" report - i need to count days, when stock was 0.
I have a transaction data: Item, Department, Date, Quantity (Quantity<0, if the company sold items. Quantity>0, if the company purchased items.)
What I do in the loading script: first of all I count stock level for each item, department and date, when the transaction was made. Secondly I count the days between transactions.
After loading the script I can write an expression and count days when Stock was 0. But the expression is not so simple, so I have explained it in the application.
THE PROBLEM: If I select a Date when there was no transaction, i can't see Zero stock of some items. For example I have transactions:
As you can see the Stock=0 from 2006.11.19 until 2007.11.17. But if I select for example year-2007 and month-2 I can't see this Item.
Maybe anyone has already solved a task like this and knows another way how to solve it, or you have some ideas how to improve my application, let me know.
Whoops! My AsOf table foolishly assumed there was only one item and department, and got really confused when you added more. It needs to include item and department as part of the key, since different dates and asofdates should be connected depending on the department and item. I THINK I have it working now. See attached.
I wouldn't put stock or date span on the transaction table. I'd probably create a stock table instead. The stock table would have a row for each date in the date span. Then just count({<Stock={0}>} distinct Date). I believe that then greatly simplifies your expressions, and allows you to see that stock was 0 even when selecting dates that had no transactions.
I thought about this solution too... but is this the best way?
Lets say i have 10000 items, 30 stores and 3 years of data. So if i make a row for each date, item and store, i'll get a table of ~330.000.000 rows. This is a really huge table...
Maybe you know some other ways, best practices to solve "Zero stock" task?
That IS a really huge table. On the other hand, the row size is tiny (three fields?) and it is probably highly compressible (same stock values date after date after date). It might even load on a 32-bit machine. If not, I bet the final result would at least fit on a 32-bit machine. In any case, if it were me I would at least test it, rather than just guessing it won't work because of the admittedly huge numbers involved.
Ultimately, though, I agree that it isn't the RIGHT solution. What if you needed to know the stock in a store at exactly 2:58:20.3021 PM? It would be impossible to generate a table with precalculated stock numbers for every ten tousandth of a second. Since that would be impossible, I consider applying the same solution to dates to ultimately be the "wrong" solution as well.
Still, it's probably the solution I would use for this case, which is why I recommended it. But yes, I'm sure there's a way to do it using your current data layout. I'd just need to think about it more.
Another approach is to go in the exact opposite direction. Don't try to store current stock anywhere, and always calculate it on the fly by summing up everything as of the selected date. For that, you would have a table like this:
If stock doesn't start at 0, you might also need an initial stock associated with each department and item. Instead of looking at a specific Date, you're then looking at an AsOfDate, which automatically connects to every transaction on or before that date, allowing you to sum them up easily.
The disadvantage compared to precalculating a stock level for each day (or after each transaction like you're doing already) is performance - to calculate days with 0 stock, it loops through every single date, and for each of those dates, adds up the quantity changes from the beginning of time to that date. I can see that getting really, really ugly if you want a chart of, say, all 10000 items across all 30 stores, reporting how many days you had 0 stock. So again, might not be what you want, but it's another idea.
I should probably break down and figure out how to just do it like you basically asked, but my mind seems to think in terms of alternatives, not in terms of just making it work.
I suspect stock is 0 instead of null since that's what's shown in the sample data, but of course you can say if(Stock=0,1) as StockZeroFlag and count({<StockZeroFlag={1}>} distinct Date). But that gives you the number of days with transactions that had no stock. I believe we want the total number of days with no stock, whether or not there were transactions on those dates.
Using an accumulated date as John suggested is a very useful solution.
I have used it several times with very good results and it's quite easy to setup using a calculated calendar.
John Witherspoon wrote: Another approach is to go in the exact opposite direction. Don't try to store current stock anywhere, and always calculate it on the fly by summing up everything as of the selected date. For that, you would have a table like this: AsOfDate, Date 2006.07.02, 2006.07.02 2006.07.03, 2006.07.03 2006.07.03, 2006.07.02 2006.07.04, 2006.07.04 2006.07.04, 2006.07.03 2006.07.04, 2006.07.02 ...
Edit: OK, debugged and working on your sample data. See attached.
Well, it's not an application, but I think this script would work. If it's not exactly right, hopefully the comments will let you know how to fix it. If not, I can create an actual example and debug it.
// Start with all dates with data (this is faster than load distinct date) AsOf: LOAD fieldvalue('Date',iterno()) as Date AUTOGENERATE 1 WHILE len(fieldvalue('Date',iterno())) ; // Min and max from this table is faster than using the real table MinMax: LOAD min(Date) as MinDate ,max(Date) as MaxDate RESIDENT AsOf ; // Set variables to the min and max dates LET vMinDate = peek('MinDate'); LET vMaxDate = peek('MaxDate');
// No longer need MinMax table DROP TABLE MinMax;
// Left join to a list of ALL dates between the min and max to get all combinations LEFT JOIN (AsOf) LOAD date($(vMinDate)+recno()-1) as AsOfDate AUTOGENERATE $(vMaxDate)-$(vMinDate)+1 ; // Inner join with condition to eliminate unnecessary combinations INNER JOIN (AsOf) LOAD * RESIDENT AsOf WHERE Date <= AsOfDate ;
