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

[Anonymous]

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.

[sunny_talwar]

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

[Oleg_Troyansky]

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!

[passionate]

Hi Deepali,

Is this what you expect?

PFA solution.

Regards,

Pankaj