3 Replies Latest reply on Jan 8, 2019 11:03 AM by Deepak Rai

    Last Sell Price

    Ryan Wilber

      All - I have a data set with sales info including selling price (as a measure).  How can a write a formula that will give me the last price a product sold for?

        • 1. Re: Last Sell Price
          Daniel Stanish

          Good day Ryan.

           

          The way I'd do it would be creating a calculation using level of detail expressions, something like this. Assuming you have fields [Product ID],[Sale Date], and [Selling Price]:

           

          Last Sale Price:

          {fixed [Product ID]: MIN(

                    IF {fixed [Product ID]: MAX([Sale Date]} = [Sale Date] THEN [Selling Price] END

              )

          }

           

          This is assuming the sale date is unique. This would be easier to show with sample data, would you happen to have any? Does this help?

           

          Best regards.

           

          -Dan

          • 2. Re: Last Sell Price
            Jim Dehner

            depends on how you set up your viz

            if you have a table and can use a table calc then use Last()

            if not find the last date with {Fixed:max(date)}

            then a conditional statement like   if [date]-[fixed max date] then avg(selling price)}

             

            you may need to add a product number to the LOD depending on what you are looking for - if you had included your twnx workbook the answer could be specific to your need

            Jim

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 3. Re: Last Sell Price
              Deepak Rai

              {FIXED Product:MAX(Date)}=Date

              drag this to Filter and Select TRUE.

              Create View with PRODUCT and PRICE. you will get only last values