Sort by latest date and summarize

[Anonymous]

Hi,

I need to sort the invoice numbers by the latest date and summarize it based on the invoice amount, I basically need to check if the total of PO amount is less than total of invoice amount, the problem I am having is that 1 PO has many invoices.

All I need is the latest invoice with sum of all invoices to tie out with the PO number. Ideal Output is attached in the file.

It's really annoying how simple things like summarizing and removing duplicates is a challenge in QV.

Please advise

Thanks

Sunny

[swuehl]

You can use FirstSortedValue().

Dimension:

PO_NO

Expressions:

=FirstSortedValue(INVOICE_NO, - INVOICE_CREATION_DT)

=Date(max(INVOICE_CREATION_DT))

=FirstSortedValue(ORG_ID,-INVOICE_CREATION_DT)

=FirstSortedValue(LE_CD,-INVOICE_CREATION_DT)

=FirstSortedValue([sum(PO_OPENING_BAL_USD)] ,-INVOICE_CREATION_DT)

=sum([sum(LINE_INVOICE_AMOUNT)])

[Anonymous]

Thanks for the response,

However I am getting - in place of any values now. Please advise

Also these expressions are wrong, why do u have 2 sums for line invoice amount

=FirstSortedValue([sum(PO_OPENING_BAL_USD)] ,-INVOICE_CREATION_DT)

=sum([sum(LINE_INVOICE_AMOUNT)])

Thanks

[swuehl]

I used the excel file you've provided. The field name is [sum(LINE_INVOICE_AMOUNT)]. It's a field name and not a sum.

Replace with your field names.

[Anonymous]

ohh my bad, thanks

however I still keep getting '-' not any value, any idea on this?

[Anonymous]

hey I got it, I had to change the date format in the script

Thanks

One more doubt, what if I had to take the oldest date, i mean in descending order, which function will work then??

[swuehl]

Could you post a more detailed view of your data?

If you have multiple records per INVOICE_CREATION_DT, FirstSortedValue will return NULL.

But you can aggregate like

=FirstSortedValue( aggr(sum(PO_OPENING_BAL_USD), INVOICE_CREATION_DT), aggr( -INVOICE_CREATION_DT, INVOICE_CREATION_DT) )