Min/Max for non-number field

[#1]

Hi everyone,

In my dashboard, I want a % CAGR KPI that updates when I select a year range. However, my year field is not formatted as numbers e.g. 2011,2012, etc but is formatted as fiscal years, e.g. FY11, FY12, etc. This means that in the expression I cannot instruct it to look for the min and max values of the current selection.

Currently, I am using this qualifier:

{<[Fiscal Year]= {"$(=min([Fiscal Year]))"}>}

Does anyone know what I can do, instead, for example just to have it search for the '12' in 'FY12'? Or do I have to change the field to numbers, e.g. 2011, 2012.

Thanks!

[Anonymous · Accepted solution]

You can format fiscal years as dual - see dual() function.

Or, take a look at maxstring() and minstring()

[sunny_talwar]

Maybe create a new field like this in the script: Right([Fiscal Year], 2) as [Year of Fiscal Year] and use that in your set analysis.

Best,

S

[#4]

It worked, thanks!

[#5]

Do you mind if you put a snapshot of the chart and the expression behind it?  I'm trying to create a similar functionality and I could use some help right now .