MULTIPLE YEARS AGGREGATION

[#1]

In the above Pivot table I am trying to Aggregate 2006 thru 2012. So The Out put should look like below, Means I wann show Report Year (Max, Max-1 ,Max-2  and Max-3 shall show me the sum of All Previous Years.  My Current Expression is :

Count({<CLAIM_TYPE_CODE_COMPARE_YTD_FLAG={'1'},Year={$(=Max(Year)),$(=Max(Year)-1),$(=Max(Year)-2)},
Report_Year={"<=$(=max(Year)-3)>=$(=min(Year)-10)",$(=Max(Year)),$(=Max(Year)-1),$(=Max(Year)-2)}>} distinct [Claim No]) .

Pls Help

Required OUTPUT

[MK_QSL]

Provide 10 line sample data !

[#3]

Ok give me a sec pls

[#4]

In the Below Sample Data I wanna see sum(Suit) for Report Year 2015,2014,2013

and Report Year (2012,2011,2010,2009,2008) summed together to show for Report  Year 2012. So my output will be

Report Year      Suit

2015                  45

2014                  77

2013                  50

2012                   279

(Sum of Report Years 2012,2011,2010,2009,2008)

Year	Report Year	Suit	NOI
2015	2015	25	30
2015	2015	20	25
2015	2014	30	29
2015	2014	25	35
2015	2014	22	35
2015	2013	10	15
2015	2013	15	39
2015	2013	25	69
2015	2012	26	65
2015	2012	26	37
2015	2012	27	36
2015	2011	22	33
2015	2011	11	32
2015	2010	63	17
2015	2009	32	38
2015	2008	36	38
2015	2008	36	35

[MK_QSL]

Create a Pivot Table

Dimension

1) Year

2) Calculated Dimension

=IF([Report Year] <= '2012','2012',[Report Year])

Expression

SUM(Suit)

[#6]

Thanx that worked partially, now I wanna make it Dynamic, instead of hard coding the  Year '2012'

Like when I select Year 2015, I wanna see

2015

2014

2013

2012 ( Sum of Prev Yrs)

When I select Year 2014, I wanna see

2014

2013

2012

2011 (Sum of Prev Years)

[MK_QSL]

Everything need to use as I suggested except calculated Dimension.

Change calculated dimension as below

=Aggr(IF([Report Year] > Max(TOTAL [Report Year])-3, [Report Year],Max(TOTAL [Report Year])-3),[Report Year])