Forum Discussion
Distinct count with date parameter
Hello everyone! Hope you all are having a nice day.
I'm looking for some help with something that is stressing me a lot in the past few days.
So, what I'm trying to do is to get the amount of lives each person had to watch since their registration date, and then compare it to how many they really watched. It's for a engagement study I'm doing.
The table should look like this:
| Name | Registration Date | [A] lives streamed since registration date | [A] lives watched | [B] lives streamed since registration date | [B] lives watched | [C] lives streamed since registration date | [C] lives watched |
| Peter | 23/08/2021 | 3 | 2 | 2 | 0 | 3 | 1 |
| Jordan | 07/11/2016 | 3 | 1 | 2 | 0 | 3 | 1 |
| Alisha | 25/10/2021 | 3 | 0 | 2 | 1 | 1 | 1 |
| Mike | 21/06/2013 | 3 | 0 | 2 | 1 | 3 | 0 |
This is the table that I'm getting the info from:
| Live Name | Date | Live | Name |
| Live [A] - 04/12/2021 | 04/12/2021 | Live [A] | Peter |
| Live [A] - 06/12/2021 | 06/12/2021 | Live [A] | Peter |
| Live [A] - 12/12/2021 | 12/12/2021 | Live [A] | Jordan |
| Live [B] - 16/03/2022 | 16/03/2022 | Live [B] | Alisha |
| Live [B] - 24/04/2022 | 24/04/2022 | Live [B] | Mike |
| Live [C] - 01/10/2021 | 01/10/2021 | Live [C] | Alisha |
| Live [C] - 04/11/2021 | 04/11/2021 | Live [C] | Peter |
| Live [C] - 01/09/2021 | 01/09/2021 | Live [C] | Jordan |
I'm using a one to many, crossfiltered relation between these tables.
If anyone knows how to get this to work and could spare some time to explain it to me, I'd really appreciate it!
Thanks in advance.
You will need to add another dimension table to deal with the blanks. Here is a sample implementation.
4 Replies
Which table has the Registration Date information? How does your data model look like?
Hello lbendlin
Sorry, I forgot about the table with the registration date. It's this one:Name Registration Date Category Peter 23/08/2021 Diamond Jordan 07/11/2016 Gold Alisha 25/10/2021 Diamond Mike 21/06/2013 Silver My data model is this:
An one to many relation with "Reg" crossfiltering the "Table".
You will need to add another dimension table to deal with the blanks. Here is a sample implementation.
