Beast Mode Question for Distinct Count

JosephC20178

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 

 

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.

 

Sincerely,

Valiant

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.
Joseph