topic Re: How to sum amount over maxstring of a dimension in QlikView

How to sum amount over maxstring of a dimension

fabrice_lattern — Thu, 06 Apr 2017 15:18:20 GMT

Dear all,

I have several budgets (with a given amount) per year, and I need to display in a table chart the last budget amount for each year.

Example of data :

What I would like to get, based on budget number :

YEAR	BUDGET NUMBER	AMOUNT
2016	B3	120
2017	B6	900

I have no problem to have the correct budget number (in dimension) :

=Aggr(MaxString([BUDGET NUMBER]), YEAR)

But I don't know how to do to get the correct amount. I tried the following expressions :

=SUM( {< [BUDGET NUMBER] ={$(=MaxString([BUDGET NUMBER]))} >} [AMOUNT])

=Sum({$<[BUDGET NUMBER]={'$(=MaxString(Aggr(MaxString([BUDGET NUMBER]), [YEAR] )))'} >}[AMOUNT])**

Any help will be really appreciated

sunny_talwar — Thu, 06 Apr 2017 15:25:23 GMT

May be like attached

sunny_talwar — Thu, 06 Apr 2017 15:27:32 GMT

Or this

Dimension

YEAR

=Aggr(If([BUDGET NUMBER] = MaxString(TOTAL <YEAR> [BUDGET NUMBER]), [BUDGET NUMBER]), YEAR, [BUDGET NUMBER])

Expression

=Sum(AMOUNT)

fabrice_lattern — Mon, 10 Apr 2017 07:18:03 GMT

Thanks a lot, it works like a charm