Solved: Select Max Year where Year format is yyyy/y - Qlik Community

terezagr · ‎2014-02-20

Hi all,

I have a list of years where the format is like YYYY/Y

so eg.

2013/4

2014/5

2015/6

I am trying to set a trigger so when user open the sheet in QlikView the latest year will be preselected and the last year too.

I have tried

Select in field-> YEAR

Search string-> =max(YEAR)

No luck…any suggestions?

Thanks T.

tresesco · ‎2014-02-20

May be like attached sample. Note, the YYYY/Y format's /Y is actually redundant and since single Y doesn't qualify a proper date format, it can be formatted with any meaning single character format like I did using YYYY/D. Ultimately, we are bothered about year.

Search string for second max year =Max(Year(Date#(Year,'YYYY/M')),2)&'/'&Right(Max(Year(Date#(Year,'YYYY/M')),2),1)

View solution in original post

alexandros17 · ‎2014-02-20

max(Left(YEAR,4))

nagaiank · ‎2014-02-20

Use the search string '=MaxString(Year)'

terezagr · ‎2014-02-20

When testing in text box, I get only 2015…but I need the 2015/6 to be selected in the list….

alexandros17 · ‎2014-02-20

The best thing is to compute year as YYYY and month as MM

to achieve this:

load:

...

myYear * 100 + myMonth as Yearmonth,

...

In this way you have only to write max(Yearmonth)

terezagr · ‎2014-02-20

Thanks, this works for the latest year…but what about the last year? MaxString(Year)-1 does not seem to work...

MK_QSL · ‎2014-02-20

=FirstSortedValue(Year,-Right(Year,1))

Clever_Anjos · ‎2014-02-20

=max({1}YEAR) as expression

Clever_Anjos · ‎2014-02-20

"latest year will be preselected and the last year too." do you have two fields?

terezagr · ‎2014-02-20

yes I do and I apply different states on those too….so the latest one will have State 1, the last year one then State 2