Problems with setanalysis expression: show end-balance per year
Hi there!
I’m pretty new with Qlikview and tried a lot to get my following expression working. I hope you can give me some expalation / help!
Situation:
What I would like to show in my “straight table chart” is that it –per year- presents me the end-balance (defined as the value of “balance” on the maximum date of that year).
Source table
Date
Balance
amount
1-1-2010
123
100
1-11-2010
2000
-100
31-12-2010
3900
100
1-11-2011
2000
-100
31-12-2011
-900
100
In my chart I have...
-a dimension YEAR (=year(date) )
-A few expressions showing me things per year
-And the thing I cannot get working correctly: that is the end-balance per year
The result (based on the table above) should be like this
Year
End-balance
2010
3900
2011
-900
The best expression I have now is this one
Max({$<date={ '$(=Max(date))'}>} balance)
But: this only returns the (correct!!) balance of the last year in my database. I want to have it for all years…
How can I get that right?
It is similar to this question but even with reading this topic, I cannot get it right. I must overlook something
Thanks for your directions! Unfortunately (as far as I see now...) it is not a solution for me.
The aggr() function looked most promising to me but results in a chart displaying no values
the firstSortedValue seems to work in _exactly_ my example, but not in my project.
I have attached a testproject with some more data (don't worry, it is only 16 records x 4 columns). Here I have modeled both options you provided (aggr and firstSortedValue) and there it is visible that it doesn't work.
For me, Celambarasan Adhimulam's answer was the best answer: usage of FirstSortedValue( ).
The final expression I now use is:
FirstSortedValue(balance,-sequencenumber);
The proposed solution with
FirstSortedValue(balance,-date);
did'nt work as expected, since the date column could have multiple records with the same value (like 4 records having value 31-12-2010). In that case, the "correct" record to pick is the one with the highest sequencenumber.
That sequencenumber is a column that I now load additionally in the loadscript...
