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,
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.
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.
complicated_calc_SM_10.5.twbx 17.1 KB