Beast Mode Question for Distinct Count
Hello Beast Mode experts,
I have a web activity dataset with IP_Address and Session_ID columns
The relation between the columns is: possible one or more Session_IDs per one IP_Address
When there is one Session_ID per one IP_Address then I count One Time Visit
When there is more than one Session_IDs per one IP_Address then I count Return Visit
I have created this (test) Beast Mode
case when Count (Distinct `session_id`) = 1 then 'Return Visit' else 'One Time Visit' end
It returns the correct text value when I use in table chart type as below but I cannot use Sum or Count for these values and it only returns Count in the Total line
IP_Address | Distinct Session ID Count | Visit Type |
11.11.11.11 | 4 | Return Visit |
11.11.11.12 | 1 | One Time Visit |
11.11.11.13 | 3 | Return Visit |
11.11.11.14 | 1 | One Time Visit |
11.11.11.15 | 1 | One Time Visit |
11.11.11.16 | 2 | Return Visit |
GRAND TOTAL | 12 | Count 6 |
Also, when using the new text field as filter I can see only the Return Visitor value which means the One Time Visitor value exists only in the table alongside the IP_Address value but not on its own.
* What woule be the correct beast mode to use to make the Distinct count per specific IP_Address and not by all ? (or is there a way to use Select in beast mode) ?
Thanks for the help
Joseph
Comments
-
Can you give a bit more information around the end result you're looking for? What are you looking to do when you mentioned "Sum or Count" your visit types and what do you want returned in your Total Line?
From my initial reaction I would suggest doing your classification into Types ahead of time in a SQL transform. (Do a count on DISTINCT Session ID by IP address and then left join that back to your original dataset on IP address and add a case when DISTINCT count > 1 then 'return' else 'one time')
Having those values ahead of time cleans up some of the card level calculations and will allow you to filter a bit easier.
I'll add more once I hear back.
Sincerely,
Valiant
0 -
Thank you, Valiant.
I was able to create the same logic using magic ETL. First to get the distinct session ID count as a new
filed. And then join it with the main table. I'm mtill trying to figue our adding date / timestemp mismatch issue.
Thank you.
Joseph0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive