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
2012Q4
Prod_1
100
2012Q4
Prod_2
101
2013Q1
Prod_1
102
2013Q1
Prod_2
103
2013Q2
Prod_1
104
2013Q2
Prod_2
105
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.
Please guide me is resolving this issue. Do we have any other way to resolve this issue?
Hope this resolve your issue. If the issue is solved please mark the answer with Accept as Solution & like it. If you want to go quickly, go alone. If you want to go far, go together.
