topic Re: Last Price Paid in QlikView

Last Price Paid

Wed, 12 Nov 2014 19:37:42 GMT

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.

Re: Last Price Paid

Wed, 12 Nov 2014 21:05:24 GMT

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.

Re: Last Price Paid

ashwanin — Thu, 13 Nov 2014 05:29:03 GMT

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.

Re: Last Price Paid

ashwanin — Thu, 13 Nov 2014 05:30:11 GMT

Please find the file.

Re: Last Price Paid

jonathandienst — Thu, 13 Nov 2014 05:38:37 GMT

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

Re: Last Price Paid

Thu, 13 Nov 2014 19:56:46 GMT

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

Re: Last Price Paid

Tue, 18 Nov 2014 01:30:28 GMT

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.

Re: Last Price Paid

jagan — Tue, 18 Nov 2014 05:14:08 GMT

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.