Last Price Paid

[#1]

How to calculate the Last price paid for a part.

I have Part = PO_LINE_ITEM_CODE as my Dimension ;

Expression “max(RECEIPT_DATE)” and “aggr(max(RECEIPT_DATE),PO_LINE_ITEM_CODE)” is giving me same dates ; that is the last time, I received this particular part.

As per me , below expression should give me the Unit price that it was bought on the last received date and that would be my last price paid for that part. 

=if(aggr(max(RECEIPT_DATE),PO_LINE_ITEM_CODE)=RECEIPT_DATE, UOM_UNIT_PRICE_USD)

But this is giving me null values, where I have data as per the database.

I also tried , below

=avg(if(aggr(max(RECEIPT_DATE),PO_LINE_ITEM_CODE)=RECEIPT_DATE, UOM_UNIT_PRICE_USD))

Just to cover, if I have multiple transactions on the same last date for that part. But still I get nulls.

Can you please help, what am I doing wrong here.

[#2]

Hi,

If you want to get the last price paid for each part you have to type:

Aggr(Max(importe),producto)

but for a part:

If(producto = 'A',  Max(importe))

producto is the column name that contains part name you want to filter.

Note: in max function you have to include the column name that contains the price paid, not the poli_line_item_code.

I hope i have help you.

[ashwanin]

Hi Jaspreet,

This can be possible with Set analysis. You just have to add max reciept date in the Expression.

Please go through the below, Might be it will solve your problem.

[ashwanin]

Please find the file.

[jonathandienst]

Hi

You have not explained how you are using this expression, but I assume its in a table or chart. The max() function will calculate the max of that value filtered by the dimension values on each row. Aggr() is incorrect in this context.

The easiest way to do this is with FirstortedValue:

     =FirstSortedValue(UOM_UNIT_PRICE_USD, -RECEIPT_DATE)

HTH

Jonathan

[#6]

Hi John

FirstSortedValue function did work , but still i am getting some null values for the last price paid.

Would you know any reason, why it is doing so.

I will check the underlined data as well to verify. Will update the discussion with my findings.

Thanks once again for the help.

Jaspreet

[#7]

FirstSortedValue worked,

FirstSortedValue(UOM_UNIT_PRICE_USD, -RECEIPT_DATE) is giving result for all the cases but not working when we have multiple records at the last receipt date, it is showing '-' for uom_unit_price_usd, i mean the last price paid. Is there a way if there are more than one records on max date then select max or avg of the price.

[jagan]

Hi,

FirstSortedValue() won't work if you have two values same, try like this

=Only(aggr(if(max(TOTAL <PO_LINE_ITEM_CODE> RECEIPT_DATE))=RECEIPT_DATE, UOM_UNIT_PRICE_USD), PO_LINE_ITEM_CODE, RECEIPT_DATE))

Hope this helps you.

If not works then attach sample file.

If you want to handle this in script then try like below

Data:

LOAD

*

FROM DataSource;

Left Join

LOAD

PO_LINE_ITEM_CODE,

Date(Max(RECEIPT_DATE) AS RECEIPT_DATE,

1 AS Flag

RESIDENT Data

Group By PO_LINE_ITEM_CODE;

Regards,

jagan.