Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
johngouws
Partner - Specialist
Partner - Specialist

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

Try this:

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

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

kamal_sanguri
Specialist
Specialist

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.

johngouws
Partner - Specialist
Partner - Specialist
Author

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

pls refer this..

sunny_talwar

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
Creator II
Creator II

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