Problem getting last invoiced price per product when more than one row

[#1]

Two invoices raised for product x
Invoice1,inv date 10-Feb-2010, price 13.6, qty 4
invoice1,inv date 10-Feb-2010, price 14, qty 10

Straight table to see the last invoice price per product.

Expression is the following:
=firstsortedvalue (PRICE, -INVOICEDATE )

Null is returned because more than one value is returned for the lowest level sort-order.
I've tried using aggr to sum the price but this didn't work... please help.

Cheers

Mark

[Anonymous]

already tried?

if(date = max(date) , prince )

or

sum ({<date= { $(=max(date))}>} prince)

[Anonymous]

aggr (if(date = max(total <dimension> date) , sum (prince) ) date, dimension)

[pover · Accepted solution]

If the question is the last invoiced price wouldn't the function be

=firstsortedvalue (PRICE, -INVOICE_NO )

in which case if you have more than one price per invoice you could try

=firstsortedvalue (distinct aggr(max(PRICE),INVOICE_NO), -INVOICE_NO)

If the invoice numbers aren't in order than maybe you could try

=firstsortedvalue (distinct aggr(max(PRICE),INVOICEDATE), -INVOICEDATE)

Regards.

Note: The problem with the the max(date) in set analysis is that it will only return values from the last day of sales of the whole store and if an item was not sold in the last day of sales than that item will not show in the list.

[johnw]

BlackburnRovers wrote:Invoice1,inv date 10-Feb-2010, price 13.6, qty 4
invoice1,inv date 10-Feb-2010, price 14, qty 10

So how are we supposed to tell, from the data you presented, which price is more recent? You may need to add an additional field of some sort to the table. That could be a time or sequence number, for instance. But unless I'm misunderstanding, you wouldn't want to add two prices together, or take the maximum price, or anything like that.

[#6]

Thanks Karl,

The following worked for me.

firstsortedvalue (distinct aggr(avg(PRICE),INVOICEDATE), -INVOICEDATE)

There is no time field so I can't see which price is latest (as John mentioned), so I had to use average.

Thanks

Mark