Help with Complex Calc!

Hi All,

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.

See Image Attached with more details.

Tried Window/Running functions but didn’t work because it duplicated records.

Got very close with a DisctinctCountOver function, but couldn’t get it to the last mile!

This works for a single stage… but haven’t been able to replace the “3” with a formula that works yet…

Thought I’d check to see if anyone could help solve!

Have you tried taking a running sum?

runningSum
(
distinct_count(opportunityid}
,[ {sortorder[opphistory]} ASC ]
)

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:

image

What data source are you using? I think it would be possible within custom SQL. But that depends on what data source you are using.

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.

1 Like

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)