5 Replies Latest reply: Feb 16, 2012 8:15 AM by Roberto Postma

# 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

Any help is appreciated!

Roberto

• ###### Problems with setanalysis expression: show end-balance per year

Hi,

Use this expression,

=FirstSortedValue(Balance,-Date)

Hope it helps

Celambarasan

• ###### Re: Problems with setanalysis expression: show end-balance per year

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.

Thanks for all the help!

• ###### Problems with setanalysis expression: show end-balance per year

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.

• ###### Re: Problems with setanalysis expression: show end-balance per year

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?