Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

Aggr on 2 max fields.

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.

 

InvoicingInvoicedInvoice IDLast PaymentDate CreationDate DueOutstanding
Company ACompany C2083131/Dec/201523/Dec/2015        200.00
Company ACompany C2346211/Feb/201623/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.

6 Replies
kamal_sanguri
Valued Contributor

Re: Aggr on 2 max fields.

Try this:

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

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

kamal_sanguri
Valued Contributor

Re: Aggr on 2 max fields.

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.

Partner
Partner

Re: Aggr on 2 max fields.

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.

kamal_sanguri
Valued Contributor

Re: Aggr on 2 max fields.

pls refer this..

Re: Aggr on 2 max fields.

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

malini_qlikview
Contributor II

Re: Aggr on 2 max fields.

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