5 Replies Latest reply on Apr 16, 2018 11:19 PM by Shinichiro Murakami

    Not MIN, not MAX, but MOST RECENT date. How?

    Michiel Van Spaandonk

      Hi all,

       

      I am trying to create a report on the quotes we offer and the value that follows from them.

      For example: we quote David bananas for $ 2.60 on 2/8/2017.

      I want to create a calculated field, that calculates the sales value, for that specific customer and specific item code, in the first 4 weeks after the quote date OR until the next quote...

       

      Michel Caissie helped me a lot in my previous post, but I'm not quite there yet (see workbook attached).

       

      The problem is that my QuoteDate formula takes the value of the FIRST quote:

      {FIXED [Customer],[Item Code]: MIN( if [isQuote] then [Booked Date & Creation Date]  end)}

       

      However, when I also quote David bananas on 1/3/2018, the QuoteDate should take the date of THAT quote, not of the first quote.

       

      Hence, the questions are:

      1. How do I take the MOST RECENT QuoteDate (for that customer, for that item)?

      2. How do I only include the value in 4 weeks after a QuoteDate OR until the next quote (for that customer, for that item)?

       

      Thanks in advance,

      Michiel