topic Maximum and 2nd Maximum Financial Year in Dimension Problem in QlikView

Maximum and 2nd Maximum Financial Year in Dimension Problem

koushik_btech20 — Fri, 06 Sep 2013 09:25:59 GMT

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...

Re: Maximum and 2nd Maximum Financial Year in Dimension Problem

swuehl — Fri, 06 Sep 2013 09:38:04 GMT

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().

Re: Maximum and 2nd Maximum Financial Year in Dimension Problem

tresB — Fri, 06 Sep 2013 09:41:33 GMT

Or,

calculated dimension as

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