RunningSum or cumulative total per day having previous users as well

Here I am trying to get daily new users, I have a dataset which collectes interactions of the app everyday and assign an app user with a unique client_id. So, I can get how many users were using the app per day by using distinct_count and get cumulative or running sum for the week, but I want to know how to get metrics on only new users but not users which occurred previous day/week/month on the given time frame.
image

1 Like

HI @Sravan_Yerupilli -

You can do something like this.

Step 1. Get the date your user first appeared using a LAC-W function
c_first_appeared_dt

minOver(dt,[{user_id}],PRE_AGG)

Step 2. Create a calculated field where if the activity date equals the first appeared then include the user_id
c_new_user

ifelse(truncDate('DD',dt)=truncDate('DD',{c_first_appeared_dt}),{user_id},null)

Step 3. Create a running_sum of the disinct count of c_new_user
c_running_new_users

runningsum(distinct_count({c_new_user}),[dt ASC])

Result:

it worked. Thanks so much!

1 Like

Hello, thank you for this. I used this solution implement the same for my use case - YTD 2022 and 2023.
When I reach Jan 2023, the formula does a cumulative distinct running total from Dec 2022. I’ve been trying to get it to start fresh from Jan 2023 again. Calculate the distinct user count at the start of the year and then onwards from there.

Thanks a lot

I suggest to use dateDiff instead of truncDate. It covers other corner cases.
ifelse(dateDiff(dt,{c_first_appeared_dt},“DD”)=0,{user_id},null)