Maximum and 2nd Maximum Financial Year in Dimension Problem

[koushik_btech20]

In my .qvw application there are three financial year 2013-2014,2012-2013 and 2011-2012.I want to show the 2013-2014 and 2012-2013 financial year in dimension of a pivot table which are the maximim and 2nd Maximum Financial Year.Anyone have the solution plese share here...

[swuehl]

Try a calculated dimension

=if( FY = maxstring(FY) or FY = maxstring(FY,2), FY)

FY being your financial year field name. And check 'Suppress When Value is Null' on dimension tab.

Alternatively, you can also use a set expression in all your expressions, create two variables using the maxstring() functions, then do it like

=sum({<FY = {$(FYMax),$(FYMax2)}>}Value)

edit: Maxstring() does not seem to support the rank argument like Max() function. But you can create your FY field using a dual value, assigning a number (like Year '2013' in '2012 - 2013') and then use max() function instead of maxstring().

[tresesco · Accepted solution]

Or,

calculated dimension as

=Aggr(If(Rank(FY)<3,FY),FY)      , and check 'Suppress When Value is Null' on dimension tab