Problems with setanalysis expression: show end-balance per year

[#1]

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

[CELAMBARASAN · Accepted solution]

Hi,

     Use this expression,

     =FirstSortedValue(Balance,-Date)

Hope it helps

Celambarasan

[jagan]

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.

[#4]

Hi Celambarasan Adhimulam and jagan mohan,

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.

What am I doing wrong?

[jagan]

Hi,

Please check the attachment for solution.

Hope this helps you.

Regards,

Jagan.

[#6]

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!