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

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

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

Michiel

• ###### 1. Re: Not MIN, not MAX, but MOST RECENT date. How?

Hi Michiel,

Doesn't MAX(Date()) return the most recent date ? - also see here Calculating Most Recent Date in the Data Set | Tableau Software

HTH

Peter

• ###### 2. Re: Not MIN, not MAX, but MOST RECENT date. How?

HI Michiel,

1.

2.

Latest date in 4 weeks

Thanks,

Shin

• ###### 3. Re: Not MIN, not MAX, but MOST RECENT date. How?

Hi Peter and Shin,

Thanks for your replies. Apparently, I have difficulties to make myself clear.

I need a something that returns the MOST RECENT quote date UP TO the (quote/order) date in the current row.

So that I can create the following calculated field:

Customer | Item Code | Quote/Order number | Sales           | "Return on quote"

David         | Bananas    | Quote 1                        | -                   | \$ 200

David         | Bananas    | Order 1                         | \$ 120           | -

David         | Bananas    | Order 2                         | \$  80            | -

This "return on quote" should give the summed value of the Orders that followed in the first 4 weeks after a quote (or until the next quote if that is within the 4 weeks), for that customer for that item code.

If we, later on, quote David for Bananas again, the QuoteDate field, show take the (new) most recent quote date.

I hope this clarifies,

Michiel

• ###### 4. Re: Not MIN, not MAX, but MOST RECENT date. How?

I'll ask it in a different way. Hopefully this will be more clear.

 Customer Item Code Order number Booked Date QuoteDate Sales Return on Quote David Bananas Quote 1 1/1/18 1/1/18 - 200 David Bananas Order 1 1/4/18 1/1/18 120 - David Bananas Order 2 1/6/18 1/1/18 80 - David Bananas Quote 2 1/9/18 1/9/18 - 100 David Bananas Order 3 1/11/18 1/9/18 100 - David Bananas Order 4 3/1/18 1/9/18 50 -

For both quotes, I want to know how much sales they generated in the 4 weeks after the quotes (for that customer, for that item), or until the next quote (if the next quote was within the 4 weeks).

For Quote 1, it should be 120 + 80 = 200, because the sales after that count for the second quote.

For Quote 2, it should only be the 100, because Order 4 was not within the 4 weeks.

• ###### 5. Re: Not MIN, not MAX, but MOST RECENT date. How?

HI Michiel

That's not good idea to try implement this type of complicated calc on Tableau.

That's seriously way easier done in excel.

I cannot explain everything because it require more than 100 steps, but attaching worksheet.

Thanks,

Shin

1 of 1 people found this helpful