Users per month in a Quarter; while some are created and some are deleted

[brwright]

How do I report total user count per month from a list of users that is constantly changing? And I want it to be dynamic so I can do it again each quarter.

DATA COLUMNS: 
USERID, CreatedAt, DELETEDAT

At the beginning of the Quarter lets say I have 10 users. 

EXAMPLE:

Start of April: 10 users, mid month 3 are added and 2 are deactivated/deleted

Start of May: 11 users, mid month 1 is added and 4 are deactivated/deleted

Start of June: 8 users, mid month 4 are added and 3 are deactivated/deleted

End of Quarter:  9 users

REPORT RESULTS:
2Q2024 April --11

2Q2024 May -- 8

2Q2024 June -- 9

I might not be framing this correctly in the first place. I attached a sample of the real data (no PII included)

[Qiu]

@brwright 
This turns out to be a very interesting one. 😁
I chose to go with a batch macro. The input contains the cutoff date for each Reporting Month, and then filter out the data consiering Create Date and Delete Date.

[Qiu · Accepted solution]

@brwright 
As you have pointed out, there is a mistake in Filter 9 in the macro. See the corrected one.

Thank you.

[KGT]

I find that the easiest way to visualise this type of thing is to have a record for each day for each employee. That massively increases the data size though which would mean that if you have a huge dataset, a batch macro will improve efficiency. In this situation, it's dev time vs usefulness as to how dynamic you make it. This solution is not totally dynamic but is getting close.

Simple solution will blow the data out to a row per day/employee using generate rows. In this example, the generate rows can be dialled in to just the relevant quarter for instance.

[Yoshiro_Fujimori]

@brwright ,

I tried this with Append Field tool. I hope this helps.

(The output is slightly different from that of @Qiu . I guess it comes from the comparison of dates at the border.)

Workflow

Formula Tool

[DELETEDAT] = 

  IF IsNull([DELETEDAT]) THEN "9999-12-31 23:59:59" ELSE [DELETEDAT] ENDIF

[ActiveAtStartOfMonth] = 

  IF [CREATEDAT] <= [StartOfMonth] AND [StartOfMonth] < [DELETEDAT] THEN 1 ELSE 0 ENDIF

[AddedInMonth] = 

  IF [StartOfMonth] <= [CREATEDAT] AND [CREATEDAT] < DateTimeAdd([StartOfMonth],1,"month") THEN 1 ELSE 0 ENDIF

[DeletedInMonth] = 

  IF [StartOfMonth] <= [DELETEDAT] AND [DELETEDAT] < DateTimeAdd([StartOfMonth],1,"month") THEN -1 ELSE 0 ENDIF

Output Image

[brwright]

Thanks everyone this s great! I'm going to walk these through today and see how each of them parse!