topic Re: Min/Max for non-number field in QlikView

Min/Max for non-number field

Mon, 09 Feb 2015 18:37:59 GMT

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!

Re: Min/Max for non-number field

Anonymous — Mon, 09 Feb 2015 18:51:28 GMT

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

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

Re: Min/Max for non-number field

sunny_talwar — Mon, 09 Feb 2015 19:20:41 GMT

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,

Re: Min/Max for non-number field

Mon, 09 Feb 2015 19:21:19 GMT

It worked, thanks!

Re: Min/Max for non-number field

Tue, 17 Feb 2015 11:01:03 GMT

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 .