Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
What is the expected output from the sample data you have shared?
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!
Hi Deepali,
Is this what you expect?
PFA solution.
Regards,
Pankaj