…which makes me think that the data needs to be structured in a particular way; I’m guessing the overall total needs to be included in the results to act as the first ‘step’? I knocked up a quick union query using the sql editor to do this:
…but it seems when I do, the Funnel chart is no longer selectable when using SQL questions?
Has anyone had any success using this chart, and if so, is there indeed a secret to structuring your data? Or is the funnel chart broken?
I’ve tried using 0.29.3 and 0.30.1, both return the same results.
Your first example is exactly like my funnel charts which each percentage being a percentage of the first step which you show to be 359 (ex 100/359=.27855). The second example is the same (ex 727/3901=.1889)
You don’t need an overall total. If you want to see an overall total and percentages of that you would want a pie chart.
This is an oldie now, but I find it's easier to give each stage a number, like with:
WHEN status = 'Making Introductions' THEN 1
WHEN status = 'Sending Proposals' THEN 2
Then aggregate everything by current status, and count how many things are in that status:
Do your select and get your results in a CTE like this:
WITH step_1 AS (
<use the case statement from above> AS status_order
COUNT(*) or COUNT(<some GUID>) AS instances
GROUP BY status
Then, count the things that are either in that status or went past that status by using a partition function when you select from step_1. Just do a window, and sum up the number of instances up to and including the one in each row. So, if you have 5 sequential steps, if you want the funnel number for step 3, you want a row that says "step 3" and has a number summing everything that's in step 3, 4, and 5. You can do it like this:
OVER(ORDER BY status_order DESC ROWS BETWEEN UNBOUNDED
PRECEDING AND CURRENT ROW) AS things_that_went_at_least_as_far_as_this_step
ORDER BY status_order;