Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Any help is appreciated!
Roberto
Hi,
Use this expression,
=FirstSortedValue(Balance,-Date)
Hope it helps
Celambarasan
Hi,
Use this expression,
=FirstSortedValue(Balance,-Date)
Hope it helps
Celambarasan
Hi,
Try this expression
Get the Year dimension in script and then use this
Use Year as Dimension and use the below expression
=Aggr(If(date = Max(date), balance), Year)
Hope this helps you.
Regards,
Jagan.
Hi Celambarasan Adhimulam and jagan mohan,
Thanks for your directions! Unfortunately (as far as I see now...) it is not a solution for me.
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.
What am I doing wrong?
Hi,
Please check the attachment for solution.
Hope this helps you.
Regards,
Jagan.
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...
Thanks for all the help!