I'm using creator to manage the product stock. I would like to make a report that shows how many a certain product are in stock. For example, on week 2 we have purchased 30 hard disk and under week 2 we have sold 5 hard disk and week 3 we have sold 6 and so on, while on week 4 we purchased more hard disks to fill the stock.
I recorded the date for each purchase (positive record) and sales (negative record). So in my database it will look like this:
Name of product Qt Date
Hard disk 30 2013-01-07
Hard disk -1 2013-01-11 (Week2 29)
Hard disk -9 2013-01-14
Hard disk -2 2013-01-16 (Week3 18)
Hard disk 15 2013-01-21
Hard disk -4 2013-01-24 (Week4 29)
What I wanna do is to get a plot or chat that shows the stock number each week, it would be 29 in week 2, 18 in week 3 and 29 in week 4. So in the X-axis should be W2, W3, W4 and the corresponding values would be 29, 18, 29.
I have tried some solutions:
- use the date (week, year) for X-axis and Qt (sum) for Y-axis however it shows only the sum for each week i.e. 29, -11 and 11 which is wrong. I want to show the overall sum by the end of each week.
- add a new column and use the actually stock number in the field and plot the date(week, year)-stock
Name of product Qt Date Stock
Hard disk 30 2013-01-07 30
Hard disk -1 2013-01-11 29
Hard disk -9 2013-01-14 20
Hard disk -2 2013-01-16 18
Hard disk 15 2013-01-21 33
Hard disk -4 2013-01-24 29
However, it sums up all the stock number for each week ie. 59, 38, 62, which is expected.
This solution will work if the following two issues are solved:
a) If I can make the filter that only show the last record in the week
b) Update the "Stock" value automatically once a new row is added or a row is updated and it should be corresponding to the date. For example if a new row is added with date 2012-12-20, all following stock values should be updated.
I hope I have explained clearly. Is that possible to create a such report or any work around for a solution?
Sincerely,
Feifei