topic Sum all values before every Month in QlikView

Sum all values before every Month

Mon, 11 Mar 2013 02:37:04 GMT

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.

Re: Sum all values before every Month

Mon, 11 Mar 2013 03:33:32 GMT

Could you give sample data file?

Karthik

Re: Sum all values before every Month

Mon, 11 Mar 2013 04:04:43 GMT

I have attached the excel file. Thanks

Re: Sum all values before every Month

nirav_bhimani — Mon, 11 Mar 2013 05:19:33 GMT

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

Re: Sum all values before every Month

Thu, 14 Mar 2013 02:53:51 GMT

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.