Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to create Stock Ending Balance every Month from Item Legder table.
Item Ledger table contains daily stock movement (stock in and stock out)
I was hoping to get the following result:
Item | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
---|---|---|---|---|---|---|---|---|---|---|---|---|
A001 | 12 | 14 | 14 | 14 | 13 | 15 | 16 | 17 | 15 | 13 | 18 | 19 |
A002 | 0 | 0 | 5 | 5 | 10 | 10 | 10 | 15 | 7 | 7 | 7 | 7 |
A003 | 100 | 50 | 50 | 30 | 20 | 50 | 20 | 20 | 20 | 30 | 30 | 30 |
A004 | 50 | 70 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
The value in January is accumulation of all values from 2008 up to the end of 2011.
The value in February is accumulation of all values from 2008 up to the end of 2011 + values in January 2012.
The value in February is accumulation of all values from 2008 up to the end of 2011 + values in January and February 2012, and so on.
I have attached excel file containing data from 2002 - 2004.
Hi
Could you give sample data file?
Karthik
I have attached the excel file. Thanks
Hi,
Try this,
In Script // QuarterStart(CalendarDate) as QuaterStartDate,
In Variable //vMaxDate= Max(CalendarDate) OR vMaxDate= (Today())
(sum(
{<
CalendarDate = {">=$(= Date (min({<CalendarWeek=, CalendarQuarter=, CalendarMonth=>} QuaterStartDate )))<=$(vMaxDate))" }
, CalendarWeek=, CalendarQuarter=, CalendarMonth=
>}
Stock))
Regards,
Nirav Bhimani
Hi Nirav,
I have tried your solution but unfortunately it didn't work.
It is quite impossible to generate report to look exactly like like illustrate before.
However, I got the workaround. I make 1 expression for each month. It was much easier.
Anyway, thanks for helping.