3 Replies Latest reply: Aug 7, 2017 12:09 PM by Pankaj Thakur

# How to get maximum Year from column having year (as int) for a particular condition ( set analysis) for particular set analysis

Hi,

I am new to qlik sense and need an expert help please.

I have a sample data as follows:

 As Of Date Account Number LoanPurposeTrend_Year Original Balance 12/31/2016 02100000 1987 174400 12/31/2016 02900000 1988 99900 12/31/2016 024500000 1987 97000 12/31/2016 027100000 1986 130000 1/31/2017 0700000 1987 180000 1/31/2017 02100000 1987 174400 1/31/2017 02900000 1988 99900 1/31/2017 026600000 1986 78600 3/31/2017 0700000 1987 180000 3/31/2017 02100000 1987 174400 3/31/2017 02900000 1988 99900 3/31/2017 021700000 1986 99000 5/31/2017 02100000 1987 174400 5/31/2017 02900000 1988 99900 5/31/2017 024500000 1987 97000 5/31/2017 027100000 1986 130000 6/30/2017 02100000 1987 174400 6/30/2017 02900000 1988 99900 6/30/2017 024500000 1987 97000 6/30/2017 027100000 1986 130000

I want original balance for max " Ltrend Year"  when particular "as of date" is selected.

Wanted to select max "LTrend Year" as my dimension for visualization. what is an expression for the same. Can someone please help with an expression.

Thanks.

• ###### Re: How to get maximum Year from column having year (as int) for a particular condition ( set analysis) for particular set analysis

What is the expected output from the sample data you have shared?

• ###### Re: How to get maximum Year from column having year (as int) for a particular condition ( set analysis) for particular set analysis

I'm assuming that you want your calculation to be made for each account, correct?

I'd try the following:

Dimension #1 = Account Number

Dimension #2 - max Year per account:

AGGR(

max(Year)

, [Account Number]

)

Measure: the balance that corresponds to the max year:

=FirstSortedValue([Original Balance], -Year)

I think this should give you the desired result. If it doesn't work as expected, please create a sample app and post it here, and I will take a look.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Boston, MA this October!

• ###### Re: How to get maximum Year from column having year (as int) for a particular condition ( set analysis) for particular set analysis

Hi Deepali,

Is this what you expect?

PFA solution.

Regards,

Pankaj