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. 

       

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

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

                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

                  • 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.

                     

                    Jaspreet

                      • 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

                            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.