Calculated field: total amount of active users based on a given users table / data set

[bizcatalin]

Hi everyone,

Currently, I have a simple dataset from my users table.

The table has the following fields:

id - UUID ( e.g ad73da65-3b21-4321-926e-6e17d2773068 )
createdAt - DATE ( e.g 2020-11-03 15:42:40.973000 +00:00 )
deactivatedAt - DATE ( e.g 2021-12-03 15:42:40.973000 +00:00 )

Currently, I have created a graph where I’m showing per month:

• amount of users created
• amount of users deactivated
• amount of total users ( using this calculated field: runningSum(sum(1), [createdAt ASC], []) )

I propose adding a fourth line per month titled ‘amount of total active users ,’ representing the cumulative sum of monthly users, excluding those who deactivated prior to that month.

For example if I have a user1 and he deactivated his account in DEC 2022, I don’t want to count him anymore starting from JAN 2023 like I do for the amount of total user graph line

Been looking at the documentation, and I think might be something with sumif/countif but not sure how to use it in this case.

Looking forward for any suggestions,
Catalin

[royyung]

You may handle it by adding a sumIf condition like below

runningSum(
sumIf(1,{DeActive Date} < {Create Date} 
 OR isNull({DeActive Date})), 
[{Create Date} ASC], [])

[bizcatalin]

Hi @royyung,

Thanks for the quick reply! Unfortunately, in the code you provided, the Deactivate Date is compared with the Created Date of the line, right?

I must compare it with the “Create Date” used in the aggregation, like in the picture below

So let’s say I have a user created in Jan 2023 and deactivated in March 2023.

I want to count that user as an active user in my system in January and February but not from March 2023 onwards. I hope it makes more sense what I want to achieve.