Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have a hierarchy table with various end dates based on quotations.
I want to display a table with the parent quotation, the initial end date and the end date of the last sub quotation.
Tihs is what it looks like if I visualise the total table.
Quote | Quote1 | Quote2 | Quote End.Date1 | Quote End.Date2 |
Q2020-765 | Q2020-765 | NULL | 28/06/2020 | 28/06/2020 |
Q2020-765-1 | Q2020-765 | Q2020-765-1 | 30/07/2020 | 30/07/2020 |
Q2020-766 | Q2020-766 | NULL | 14/06/2020 | 14/06/2020 |
Q2020-766-1 | Q2020-766 | Q2020-766-1 | 28/06/2020 | 28/06/2020 |
Q2020-767 | Q2020-767 | NULL | 15/07/2020 | 15/07/2020 |
Q2020-767-1 | Q2020-767 | Q2020-767-1 | 30/07/2020 | 30/07/2020 |
The formula I use is
=if(QuoteParentID='NULL', [Quote End.autoCalendar.Date],
Max({$<QuotationID={$(=Max(QuotationID))},[Quote Status]-={"Cancelled","Rejected"}>}[Quote End.autoCalendar.Date]))
The way I want the table to show is without the subquotes (only parent quote) and the inital end date and final end date on the same line. As below example.
Quote | Quote End.Date | Quote End.Date2 |
Q2020-765 | 28/06/2020 | 30/07/2020 |
Q2020-766 | 14/06/2020 | 28/06/2020 |
Q2020-767 | 15/07/2020 | 30/07/2020 |
Anybody can help me with the correct formula please.
Thanks in advance.
Paul