Forum Discussion
distnict count based on multiple column
I would like to check repeated values in my table based on Day and location column. Here is my Sample data:
| VisitorID | Day | Location |
| 1501 | 1 | Location 1 |
| 1501 | 1 | Location 2 |
| 1501 | 1 | Location 3 |
| 1159 | 1 | Location 1 |
| 1152 | 2 | Location 1 |
| 1880 | 3 | Location 4 |
| 1410 | 1 | Location 1 |
| 1610 | 1 | Location 1 |
| 1610 | 1 | Location 2 |
| 1501 | 2 | Location 1 |
| 1501 | 3 | Location 1 |
| 1632 | 1 | Location 1 |
| 1632 | 1 | Location 2 |
| 1632 | 1 | Location 3 |
| 1632 | 2 | Location 4 |
| 1632 | 3 | Location 4 |
| 1632 | 4 | Location 1 |
And I am looking for output like:
| Repeat Days | Distinct Count of VisitorID |
| 1 Day Only (Not Repeated) | 4 |
| 2 Day Repeat | 4 |
| 3 Day Repeat | 2 |
| 4 Day Repeat | 1 |
In Single day (any day) Location is not repeating but event day is repeating since a visitor can go in to different location in a day. Can we create a mesure that will calculate Distinct Count of VisitorID based on these 2 columns,
I have multiple columns in my table.
Thanks, Kul
Hi,
I dont think your result matches the dataset you provided?
In file i added to this reply I grouped your data on location and then did a distinctcount on visitor by day.
https://drive.google.com/file/d/1K-Vf63La87CMhaZJI9fnRo3FY-hi99Df/view?usp=sharing
I hope this is what you were looking for.
Robbe
1 Reply
Hi,
I dont think your result matches the dataset you provided?
In file i added to this reply I grouped your data on location and then did a distinctcount on visitor by day.
https://drive.google.com/file/d/1K-Vf63La87CMhaZJI9fnRo3FY-hi99Df/view?usp=sharing
I hope this is what you were looking for.
Robbe