Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My table has the stock movement for each item by day. So at any point if I want to find the current stock, I need to sum all the stock movements from the day business started.
Now I want to create a report which shows the stock level over a period of time. The period can be selected by the user.
Here is a simplified version of my table:
Stock:
LOAD * INLINE
[Brand,Item,Reason,Date,StockMovement
EveLom,E001,OpeningStock,01/01/2014,200
EveLom,E001,Sale,05/01/2014,-100
EveLom,E001,Purchase,01/03/2014,100
EveLom,E001,Sale,01/05/2014,-50
EveLom,E001,Sale,01/10/2014,-50
EveLom,E001,Sale,21/10/2014,-20
EveLom,E001,Sale,01/01/2015,-30
EveLom,E001,Sale,01/03/2015,-10
EveLom,E001,Purchase,01/04/2014,100
EveLom,E002,OpeningStock,01/01/2014,2000
EveLom,E002,Sale,05/01/2014,-100
EveLom,E002,Purchase,01/03/2014,100
EveLom,E002,Sale,01/05/2014,-50
EveLom,E002,Sale,01/10/2014,-50
EveLom,E002,Sale,01/11/2014,-20
EveLom,E002,Sale,01/01/2015,-30
EveLom,E002,Sale,01/02/2015,-10
EveLom,E002,Purchase,01/03/2014,100
LQ,L001,OpeningStock,01/01/2014,1000
LQ,L001,Sale,05/01/2014,-100
LQ,L001,Purchase,01/03/2014,100
LQ,L001,Sale,01/05/2014,-50
LQ,L001,Sale,01/10/2014,-50
LQ,L001,Sale,21/10/2014,-20
LQ,L001,Sale,01/01/2015,-30
LQ,L001,Sale,01/03/2015,-10
LQ,L001,Purchase,01/04/2014,100
LQ,L002,OpeningStock,01/01/2014,5000
LQ,L002,Sale,05/01/2014,-100
LQ,L002,Purchase,01/03/2014,100
LQ,L002,Sale,01/05/2014,-500
LQ,L002,Sale,01/10/2014,-500
LQ,L002,Sale,01/11/2014,-200
LQ,L002,Sale,01/01/2015,-300
LQ,L002,Sale,01/02/2015,-100
LQ,L002,Purchase,01/03/2014,100
];
There four items and they are grouped under 2 brands.
Now I want a stacked bar chart for Stock Level by Brand Vs Month.
The chart should look like below. (I created this one for sales.)
Months will be in X-Axis.
Stock will be in Y-Axis.
I have attached a qvw document with the sample data and calendar.
I tried several methods without any success. Any ideas/guidance will be much appreciated.
Hi Mayuran, please check attachment, I added a YearMonth field and I didn't worry about design, only to get the values.
Hope this helps you!
added this column to the table you used in your upload. I think it achieves what you want, but I am not sure. Please check:
= RangeSum(Above(sum(StockMovement), 0, RowNo()))
I can't get your chart to work yet, but I think these are at least the numbers you wanted, right?
Hi Mayuran, please check attachment, I added a YearMonth field and I didn't worry about design, only to get the values.
Hope this helps you!
Here is one without modifying the backend script
Thanks for the prompt response.
This works but if you select year 2015, the values are not correct as the stock should be calculated from 2014 Jan.
Thanks for the prompt response.
This just sums up the values for the current year and not shows the running total.
Thank you for the answer. This works
Ideally I am looking for an answer that does not touch the backend script. If I cannot find any I will use this solution.
Once again thanks.
here is with using the full accumulation
See this blog post for some information on accumulation:
It will be hard without a 'Date' or 'YearMonth' field, for complete years can be done but if the user selects 2nd semester of 2014 an first semester of 2015 I don't see a way to use set analisys without a field that combines month and year in any way.