Table B contains the list of state changes that each call has forego.
| id | stateA | stateB |
| A | S0 | S1 |
| A | S1 | S2 |
| A | S2 | S4 |
| B | S0 | S1 |
| B | S1 | S4 |
| C | S0 | S2 |
| C | S2 | S3 |
| C | S3 | S4 |
| D | S0 | S1 |
| D | S1 | S2 |
| D | S2 | S5 |
I’m building a Sankey diagram with the state transitions defined in table B, but I am failing to calculate simple metrics (averageCallDuration) when filtering by dimensions 1 & 2 because of the LEFT JOIN.
How do I need to calculate the averageCallDuration so that I can filter on the Sankey Diagram and still get the correct KPI?
Yes, that’s right. Because each call goes through a different number of states. Hence, calls with more states contribute more to the average…
I didn’t want to make the calculation beforehand because I want to calculate the average duration of the calls after filtering by state (which leaves a lot of registries out each time the filter is applied and makes the beforehand calculation unusable)
Ex (after filtering in the Sankey Diagram the path S0-S1):
| id | stateA | stateB |
| A | S0 | S1 |
| B | S0 | S1 |
| D | S0 | S1 |
My guess is that the calculation has to be dynamic, taking into account the number of registries “not filtered”…