Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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?
Thanks in advance.
PFA