topic Re: Sum(amount) based on max(value) in QlikView

Sum(amount) based on max(value)

tcarolus — Wed, 25 Nov 2020 16:16:04 GMT

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!

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

marcus_sommer — Wed, 15 Feb 2017 11:02:38 GMT

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

- Marcus

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

sasikanth — Wed, 15 Feb 2017 11:56:30 GMT

Also try this

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

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

tcarolus — Wed, 15 Feb 2017 23:23:02 GMT

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

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

soloeeeoff — Thu, 16 Feb 2017 07:34:33 GMT

Hi Tamarah,

Please try this logic.

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

Thanks.