sum of Previous totals

[#1]

Hi,

I have a straight table which has two dimension, one is year quarter which values are like 2011Q1, 2011Q2 etc and second dimension is product type like Prod_1 and Prod_2.

Now what I want to show is the for each year quarter, for each product what is the sum of production for last 4 quarters.

For example:

If I am calculating for year quarter 2012Q4, then I need to take sum of 2012Q4, 2011Q4, 2011Q3, 2011Q2 for both products. So the output will be like this:

YearQuarter	Prod_Type	Quantity
2011Q1	Prod_1	100
2011Q1	Prod_2	101
2011Q2	Prod_1	102
2011Q2	Prod_2	103
2011Q3	Prod_1	104
2011Q3	Prod_2	105
2011Q4	Prod_1	106
2011Q4	Prod_2	107
2012Q1	Prod_1	108
2012Q1	Prod_2	109

Here this is a sample data for understanding. So for yearquarter 2012Q4 and for product type Prod_1, the quantity is sum of previous three yearquarter and current yearquarter ( 2012Q4 + 2012Q3 + 2012Q2 + 2012Q1). So in expression, I am using below formula

Sum (Quantity)

+

Above(Sum (Quantity), 1)

+

Above(Sum (Quantity),2)

+

Above(Sum (Quantity),3)

But this is not working because as per my understanding, we have two dimensions and here above function is getting confused which dimension to take. When I am removing prod type dimension, it is working. But I need to show product type in output so I can not remove it.

Please guide me is resolving this issue. Do we have any other way to resolve this issue?

[er_mohit]

Try this

rangesum(above(total sum(Quantity),0,rowno(Total)))

[sushil353]

Hi,

PFA,

Hope you will get idea to use above and rangesum function.

HTH

Sushil

[jonathandienst]

Hi

This should work:

RangeSum(Above(TOTAL Sum(Quantity), 0, 4))

HTH

Jonathan

[#5]

hi try this

rangesum(above(total sum(Quantity),0,rowno(total)))

[#6]

Hi Nirmal,

Attached sample file, Please have a look.

YearQuarter	Prod_Type	Total
		0
2013Q2	Prod_2	510
2013Q2	Prod_2	615

But my req is

YearQuarter	Prod_type	Total
2013Q1	Prod_1	615
	Prod_2

Thanks,

Vamsi

[#7]

will you upload sample application