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|
|18.104.22.168||1||One Time Visit|
|22.214.171.124||1||One Time Visit|
|126.96.36.199||1||One Time 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
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.
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.
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 472 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 202 Visualize
- 255 Beast Mode
- 2.1K Charting
- 12 Variables
- 83 Cards, Dashboards, Stories
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 179 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive