topic Re: Aggr on 2 max fields. in QlikView

Aggr on 2 max fields.

johngouws — Mon, 05 Jun 2017 09:04:07 GMT

Hello,

I have been searching other AGGR questions but cannot 'understand' what the solution needs to be. I have data as below, where there are outstanding values for a customer. the 2 inv id's are 2083 and 2346. The outstanding values are carried forward from 2083 to 2346. I need to show only the most resent inv id with its outstanding value. (Inv 2346)

My closest expression is "sum(Aggr(max([Outstanding]), [Last Payment]))", but it returns 2 rows instead of only the last record.

Invoicing	Invoiced	Invoice ID	Last Payment	Date Creation	Date Due	Outstanding
Company A	Company C	2083	1	31/Dec/2015	23/Dec/2015	200.00
Company A	Company C	2346	2	11/Feb/2016	23/Feb/2016	200.00

I really appreciate a explanation about what I am doing wrong, so that I can understand how the aggr works.

Thank you.

Re: Aggr on 2 max fields.

kamal_sanguri — Mon, 05 Jun 2017 09:20:17 GMT

Try this:

=Max(Aggr(Sum({<[Last Payment] = {"$(=Max([Last Payment]))"}>}[Outstanding]),[Last Payment]))

--Correction done in expression - forgot to include " before $

Re: Aggr on 2 max fields.

kamal_sanguri — Mon, 05 Jun 2017 09:22:23 GMT

you may want to replace [Last Payment] in above expression with [Invoice ID], If you want to take most recent invoice code.

For reference attaching a QV doc.

Re: Aggr on 2 max fields.

johngouws — Mon, 05 Jun 2017 10:02:25 GMT

Hello Kamal.

Thanks for your reply. Your solution returns only the last record for the whole data set. Probably was not clear from my side. Sorry. I require the outstanding value for each 'Invoiced' company. The example only had Company C as a example but there are many more companies.

Thank you.

Re: Aggr on 2 max fields.

kamal_sanguri — Mon, 05 Jun 2017 11:28:27 GMT

pls refer this..

Re: Aggr on 2 max fields.

sunny_talwar — Mon, 05 Jun 2017 11:35:15 GMT

May be this

Dimensions

Invoicing

Invoiced

Expressions

FirstSortedValue([Invoice ID], -[Last Payment])

Max([Last Payment])

FirstSortedValue([Date Creation], -[Last Payment])

FirstSortedValue([Date Due], -[Last Payment])

Re: Aggr on 2 max fields.

malini_qlikview — Mon, 05 Jun 2017 11:59:28 GMT

Hi,

To answer your question of what's wrong in your Expression 'sum(Aggr(max([Outstanding]), [Last Payment]))'

Your Requirement is to find out the Outstanding amount for the latest payment , so latest payment =Max(Last Payment) is required in your expression rather than max(outstanding). in your expression you are finding out max of Outstanding for each last payment which will give two records.

you need a expression to find out the max last payment for every client and the equivalent outstanding amount.

simply use the below for your requirement,

Sum({<[Last Payment] = {"$(=Max([Last Payment]))"}>}[Outstanding])