Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Partner
Partner

Sum(amount) based on max(value)

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
105895105895217-12-20132660,52
105895316-1-20144152,5
105895421-7-20142700

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!

1 Solution

Accepted Solutions
Partner
Partner

Re: Sum(amount) based on max(value)

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))

View solution in original post

4 Replies
MVP & Luminary
MVP & Luminary

Re: Sum(amount) based on max(value)

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

- Marcus

sasikanth
Valued Contributor III

Re: Sum(amount) based on max(value)

Also try this

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

Partner
Partner

Re: Sum(amount) based on max(value)

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))

View solution in original post

Highlighted
soloeeeoff
New Contributor III

Re: Sum(amount) based on max(value)

Hi Tamarah,

Please try this logic.

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

Thanks.