Sum all values before every Month

[#1]

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:

2008, 2009, 2010, 2011, 2012 (2012 is selected)

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.

[#2]

Hi

Could you give sample data file?

Karthik    

[#3]

I have attached the excel file. Thanks

[nirav_bhimani]

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

[#5]

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.