Sum(amount) based on max(value)

[tcarolus]

Hi all,

I'm having trouble figuring the following out.

I have the following situation. Imagine a travel agency with bookings (bookid), different versions per bookid (bookversid), different bookdates per version (bookdate) and different amount per version, amountCY. See table below:

BookId	bookversid	Bookdate	Amount CY
105895	1058952	17-12-2013	2660,52
	1058953	16-1-2014	4152,5
	1058954	21-7-2014	2700

With no selection on bookdate I want to sum the amountCY of the latest bookdate. In this case 2700,-

If my selection on Bookdate is >= 16-1-2014 I want amount CY 2700 as a result.

With a Bookdate >= 17-12-2013, <= 16-1-2014 I want amount CY 4152,50 as a result.

The last thing I tried was:

Max(aggr(Sum(AmountCY),bookversid))

But that does no give me the result I want. With a Bookdate selection of >1-1-2014 this formula returns two rows, 16-1-2014 and 21-7-2014. But I want only the last row.

I hope somebody can help me out here.

Thank you so much in advance!

[marcus_sommer]

Try it with: firstsortedvalue([Amount CY], -Bookdate). Also the use of the bookversid might be working in this case.

- Marcus

[sasikanth]

Also try this

=FirstSortedValue(Aggr(sum([Amount CY]),BookId,bookversid) ,-  Aggr(max(Bookdate),BookId,bookversid))

[tcarolus · Accepted solution]

Marcus and sasi thank u so much for sharing your ideas.

I must say my colleague helped me out and he found the solution with the following formula:

Sum(If(Aggr(NODISTINCT Max(bookversid),BookId)=bookversid,AmountCY))

[soloeeeoff]

Hi Tamarah,

Please try this logic.

=Sum( {<Bookdate={'=Bookdate=$(=max(Bookdate))'}>}  [Amount CY])

Thanks.