Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

dgadge_inv
New Contributor

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 DateAccount NumberLoanPurposeTrend_YearOriginal Balance
12/31/2016021000001987174400
12/31/201602900000198899900
12/31/2016024500000198797000
12/31/20160271000001986130000
1/31/201707000001987180000
1/31/2017021000001987174400
1/31/201702900000198899900
1/31/2017026600000198678600
3/31/201707000001987180000
3/31/2017021000001987174400
3/31/201702900000198899900
3/31/2017021700000198699000
5/31/2017021000001987174400
5/31/201702900000198899900
5/31/2017024500000198797000
5/31/20170271000001986130000
6/30/2017021000001987174400
6/30/201702900000198899900
6/30/2017024500000198797000
6/30/20170271000001986130000

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.

 

3 Replies
MVP
MVP

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?

MVP
MVP

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!

passionate
Valued Contributor

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