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_AddressDistinct Session ID CountVisit Type Visit Time Visit Visit Time Visit Time Visit Visit

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




  • Valiant

    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.




  • JosephC20178

    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.