Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Provide 10 line sample data !
Ok give me a sec pls
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 |
Create a Pivot Table
Dimension
1) Year
2) Calculated Dimension
=IF([Report Year] <= '2012','2012',[Report Year])
Expression
SUM(Suit)
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)
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])