Here is my scenario… I have a Sales team that wants to see a sales funnel, but the issue is that sales reps ‘skip stages’ and they want me to fill those in for the funnel and I can’t seem to create a calculation that satisfies.
Hey! Thanks for looking at this. Yah, I did try that, I guess maybe because it is a table function it just adds the rows so the result looks like this:
Using redshift, so could use SQL. Would love to hear your thoughts. Was hoping to do it in Quicksight so I could be able to filter on data source, but this would be a good alternative if not possible.
I would thing a SUM(count( ####)) over (order by stage) could work. But not sure how your data is actually looking and if you need a partition by salesperson for example. If you could provide a table with sample data, I could help writing the sql function.
Thank @Jeroen_O , I figured it out. Here is the SQL query I ended up using.
WITH sortorder AS (SELECT DISTINCT orgid, sortorder, stagename FROM opphistory)
SELECT DISTINCT s.orgid as orgidopphistory, s.sortorder, s.stagename, opportunityid FROM sortorder s LEFT JOIN opphistory h ON s.sortorder <= h.sortorder and s.orgid = h.orgid AND (isclosed = 0 OR iswon = 1)