Forum Discussion
alya1
Helper V
Helper VHelp with customer turnover, combining 2 tables with missing dates
Hi all,
I've been trying different ways to go about this with no luck, thank you for your time.
I have 2 tables like below:
| table 1 customers list | ||
| customer id | subscription tier | date |
| 111 | a | 4/3/2021 |
| 222 | a | 4/3/2021 |
| 333 | a | 4/3/2021 |
| 111 | a | 4/21/2021 |
| 222 | a | 4/21/2021 |
| 333 | a | 4/21/2021 |
| 111 | c | 4/22/2021 |
| 222 | a | 4/22/2021 |
| 333 | a | 4/22/2021 |
| 444 | b | 4/22/2021 |
| 222 | a | 5/2/2021 |
| 333 | a | 5/2/2021 |
| 444 | b | 5/2/2021 |
| 555 | a | 5/2/2021 |
| 111 | b | 10/1/2023 |
| 222 | a | 10/1/2023 |
| 333 | a | 10/1/2023 |
| 444 | b | 10/1/2023 |
| 555 | a | 10/1/2023 |
and
| table 2 customers actions | ||||
| customer id (CA) | action (CA) | subscription tier (CA) | date (CA) | reason (CA) |
| 111 | activated | a | 1/1/2021 | sale |
| 222 | activated | a | 4/1/2021 | sale |
| 333 | activated | a | 4/1/2021 | sale |
| 444 | activated | b | 4/22/2021 | friend |
| 111 | deactivated | c | 4/28/2021 | price |
| 555 | activated | a | 5/1/2021 | friend |
| 111 | reactivated | b | 9/30/2023 | sale |
both these tables start in FY 2021 till now with a lot more data.
I am having trouble creating graphs that make sense when I try to connect these 2 tables based on date.
It's fine if I'm just showing data from 1 table but when I try to show turnover (# deactivated customer id (CA) / # customer id ) based on specific time period the number look very wrong, probably because of all the missing dates...
Does anyone have idea on what is the correct way to go about this please?
