Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 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))
Try it with: firstsortedvalue([Amount CY], -Bookdate). Also the use of the bookversid might be working in this case.
- Marcus
Also try this
=FirstSortedValue(Aggr(sum([Amount CY]),BookId,bookversid) ,- Aggr(max(Bookdate),BookId,bookversid))
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))
Hi Tamarah,
Please try this logic.
=Sum( {<Bookdate={'=Bookdate=$(=max(Bookdate))'}>} [Amount CY])
Thanks.