I’m trying to create a BAQ that will compare Quote/PartNum to SO/PartNum.
I get a list of parts quoted within a certain timeframe. I then check to see if that part was entered into a SO within 90 days of the quote.
I have a BAQ that returns a list of Quotes and Parts (subquery1) and then a list of Orders and Parts (subquery2) which somewhat works. The issue being that if the part were ordered multiple times off of the same quote, I will get multiple lines.
Ultimately I am trying to create a BAQ that allows me to calculate a success rate for these quotes which is why I only want 1 Quote - 1 True or False for the SO. (The built-in win/loss doesn’t work for us) .
The total time window and the 90 day window may change, but I can handle that.
1 Like
Steve, I see now what is happening. Let me think about it for a sec.
Safe to assume you aren’t converting quotes to orders if you aren’t using the built in Win/Loss? Doing it all in a BAQ the way you are will be pretty tough you would have to get really creative. Any opportunity to add a QuoteNum_c UD field to the order line that you associate a line with the quote it came from. Link those together in your time frame?
1 Like
Built in win loss is only there if you have CRM, isn’t it? I believe if you convert quote to order or link order to quote line, that process will bring over the quote line info to order detail table.
From there you can join order dtl to quote dtl to see if you won a quote.
The issue is that sometimes a part is linked to many orders.
I posted earlier that he could just group orderdetail table by company and quote num and then join that back to the quote dtl table.You could even group the order detail table by quote num and quote line and then you would know which quote lines were won.
I already have a query that returns Orders for parts that have been quoted. The main issue is when (as utaylor mentioned) there are multiple orders per quote.
I really don’t care if there’s one order for 1 piece or 20 orders for thousands of pieces. (Not interested in $ either)
I only want to know if a quote generated an order. (Yes, we are making a few assumptions. 1 being that a part ordered after the quote is because of the quote. We can live with that.)
Right now I have CustID, Name, QuoteNum, Date Quoted, PartNum from 1 query (InnerSubQuery) and SONum, Line, OrderDate, from the second query (CTE).
It could be just CustID, Name, QuoteNum, Date Quoted, PartNum, True or False.
Steve,
What I am saying is you should group the order detail table by OrderDtl.Company,OrderDtl.QuoteNum,OrderDtl.Quoteline and then join that to your quote dtl table.
Oh, make sense. Let me give that a try.
Thanks
Yes, and you may wish to use a LEFT OUTER JOIN to make sure that quote lines that were not won also show up so you can do a total win and loss with one dataset.
You could then group your top query by quotenum and then do a calculated field on the subquery.company field like Sum(Coalesce(Subquery.Company,0)).
That way if the sum of your calculated field is grater than 0 it means the quote was won because one or more of the lines were linked to an order.
I know also if the quote line gets rolled in to a order it has a checkbox called ordered on it. Also the Quotehed has a ordered checkbox on it if the whole quote gets ordered. I would look there and test that out as another option.
1 Like
There you go.
I finally had a chance to go back and work on this.
I needed to make a few assumptions - for example, it wasn’t a win for the quote if the order wasn’t within 3 months.
But, with 2 CTE, 1 regular subquery and a top level query, it’s working.
(The ‘Win’ assumptions were agreed upon by all involved.
We all know it’s not perfect, but it gets us in the ballpark.)
Thanks to all for your ideas.
1 Like
There has to be assumptions here. I remember doing this exact same query to get a win rate and we had to assume X Y and Z.
Glad you succeeded!