Beast mode calculation based on Time range

Akhil
Akhil Member
in Magic ETL

I have a data set which consists of two columns - user_Id and visit_date on the site.

the user id remains same for each user i.e if a user login in January for the first time and user_Id 123 is assigned, then for all the subsequent visits on site, the user_id would remain the same irrespective of the visit_Date.

 

My requirement is calculate no. of new distinct user_id each month i.e How many users logged in a specific month and their records are not present for any previous months data.

 

Logic:

For any specific month > Count(DISTINCT(user_ID)) where user_ID not in ( distinct(user_id))  from all prior months to the month in question)

 

Unfortunately, I could not find a way to build this logic in Beast mode. 

Any help on this would be appreciated.

 

Data Columns available:

user_id , visit_date.

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    You can't do it in Domo with beast modes with the data model that you have without listing out each user in your card.

     

    What you can do is create an ETL (or data view if you have the beta) and add a window function column that calculates the min(login_date) over (userID). This would be your 'first login event ever'.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    You can't do it in Domo with beast modes with the data model that you have without listing out each user in your card.

     

    What you can do is create an ETL (or data view if you have the beta) and add a window function column that calculates the min(login_date) over (userID). This would be your 'first login event ever'.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Akhil
    Akhil Member

    Thank you @jaeW_at_Onyx  this is the closest and best solution to my requirement.

Categories