topic Re: Sort by latest date and summarize in QlikView

Sort by latest date and summarize

Anonymous — Tue, 02 Jun 2015 12:27:14 GMT

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

Re: Sort by latest date and summarize

swuehl — Tue, 02 Jun 2015 13:57:52 GMT

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

Re: Sort by latest date and summarize

Anonymous — Tue, 02 Jun 2015 14:33:49 GMT

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

Re: Sort by latest date and summarize

swuehl — Tue, 02 Jun 2015 14:46:39 GMT

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.

Re: Sort by latest date and summarize

Anonymous — Tue, 02 Jun 2015 14:51:24 GMT

ohh my bad, thanks

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

Re: Sort by latest date and summarize

Anonymous — Tue, 02 Jun 2015 14:59:25 GMT

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

Re: Sort by latest date and summarize

swuehl — Tue, 02 Jun 2015 15:00:29 GMT

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