7 Replies Latest reply: Nov 18, 2014 12:14 AM by jagan mohan rao appala RSS

    Last Price Paid

    Jaspreet Gujral

      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. 




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


      I also tried , below



      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
          Julisy Amador



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



          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
            Ashwani Kumar

            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
              Ashwani Kumar

              Please find the file.

              • Re: Last Price Paid
                Jonathan Dienst



                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)




                  • Re: Last Price Paid
                    Jaspreet Gujral

                    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.



                      • Re: Last Price Paid
                        Jaspreet Gujral

                        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 mohan rao appala



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



                            Hope this helps you.

                            If not works then attach sample file.

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




                            FROM DataSource;

                            Left Join



                            Date(Max(RECEIPT_DATE) AS RECEIPT_DATE,

                            1 AS Flag

                            RESIDENT Data

                            Group By PO_LINE_ITEM_CODE;