Counting New and Exited IDs Between Months

[adh79]

Hi everyone,

I'm need some help with counting IDs that have either joined or exited between months. Here's my input table i Qlik Sense load:

OriginalData:
LOAD * INLINE [
Department, Date, ID
KOBI, 01-01-2023, 12345
KOBI, 01-01-2023, 12346
KOBI, 01-01-2023, 12347
KOBI, 01-01-2023, 12348
KOBI, 01-01-2023, 12349
KOBI, 01-01-2023, 12350
KOBI, 01-02-2023, 12345
KOBI, 01-02-2023, 12346
KOBI, 01-02-2023, 12347
KOBI, 01-02-2023, 12350
KOBI, 01-02-2023, 12351
KOBI, 01-02-2023, 12352
KOBI, 01-02-2023, 12354
KOBI, 01-03-2023, 12356
KOBI, 01-03-2023, 12357
KOBI, 01-03-2023, 12358
KOBI, 01-03-2023, 12359
];

In this dataset, each row represents an ID for a department on a particular date (always the 1st of each month). My goal is to calculate two things for each month:

1. Joined IDs: IDs that exist in the current month but did not exist in the previous month.
2. Exited IDs: IDs that exist in the current month but will not exist in the next month.

I've tried various approaches using ApplyMap and Exists, but I haven't been able to get consistent results.

Thanks in advance for your help!

[TcnCunha_M]

Load your data sorting by the Id , Department, Date

Make a if Previous (department) = Department and  Previous (Id ) = ID  and Previous (Date) <> Date, 1, 0) as Joined IDs

second case you might need to do a loop load , load the month, then the future month (if exist) and  left join by Id+ Department as key, if where is connection  that means exist in future month

[Kushal_Chawda]

@adh79  How would you Flag Joined and Exited in your example data?

[adh79]

Hi,

Flag like this:

Department Date ID ExistsInPrevMonth NotExistsInNextMonth
KOBI 01-01-2023 12345 0 0
KOBI 01-01-2023 12346 0 0
KOBI 01-01-2023 12347 0 0
KOBI 01-01-2023 12348 0 1
KOBI 01-01-2023 12349 0 1
KOBI 01-01-2023 12350 0 0
KOBI 01-02-2023 12345 1 1
KOBI 01-02-2023 12346 1 1
KOBI 01-02-2023 12347 1 1
KOBI 01-02-2023 12350 1 1
KOBI 01-02-2023 12351 0 1
KOBI 01-02-2023 12352 0 1
KOBI 01-02-2023 12354 0 1
KOBI 01-03-2023 12356 0 0
KOBI 01-03-2023 12357 0 0
KOBI 01-03-2023 12358 0 0
KOBI 01-03-2023 12359 0 0

[Kushal_Chawda · Accepted solution]

@adh79  try below

OriginalData:
LOAD *
INLINE [
Department, Date, ID
KOBI, 01-01-2023, 12345
KOBI, 01-01-2023, 12346
KOBI, 01-01-2023, 12347
KOBI, 01-01-2023, 12348
KOBI, 01-01-2023, 12349
KOBI, 01-01-2023, 12350
KOBI, 01-02-2023, 12345
KOBI, 01-02-2023, 12346
KOBI, 01-02-2023, 12347
KOBI, 01-02-2023, 12350
KOBI, 01-02-2023, 12351
KOBI, 01-02-2023, 12352
KOBI, 01-02-2023, 12354
KOBI, 01-03-2023, 12356
KOBI, 01-03-2023, 12357
KOBI, 01-03-2023, 12358
KOBI, 01-03-2023, 12359
];

Left Join(OriginalData)
Load max(FieldValue('Date',RecNo())) as max_date
AutoGenerate FieldValueCount('Date');

New:
Load *,
    if(ID<>Previous(ID),0,1) as ExistsInPrevMonth   
Resident OriginalData
Order by ID,Date;

Drop Table OriginalData;

Final:
Load *,    
     if(ID=Previous(ID) or Date = max_date,0,1) as NotExistsInNextMonth  
Resident New
Order by ID,Date desc;

Drop Tables New;

[adh79]

A big thank you to everyone who helped with this solution. The approach using Previous() and max_date was exactly what I needed to compare IDs across months and handle large datasets efficiently. It works perfectly now!