Beast Mode Question for Distinct Count

JosephC20178
JosephC20178 Member
in Archive

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
11.11.11.114Return Visit
11.11.11.121One Time Visit
11.11.11.133Return Visit
11.11.11.141One Time Visit
11.11.11.151One Time Visit
11.11.11.162Return Visit
GRAND TOTAL12Count 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

  • BlueRooster
    BlueRooster Domo Employee

    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
    JosephC20178 Member

    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 

Categories