topic Re: Counting New and Exited IDs Between Months in App Development

Counting New and Exited IDs Between Months

adh79 — Mon, 23 Sep 2024 11:25:15 GMT

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:

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:

Joined IDs: IDs that exist in the current month but did not exist in the previous month.
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!

Re: Counting New and Exited IDs Between Months

TcnCunha_M — Tue, 24 Sep 2024 10:30:53 GMT

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

Re: Counting New and Exited IDs Between Months

Kushal_Chawda — Tue, 24 Sep 2024 10:56:59 GMT

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

Re: Counting New and Exited IDs Between Months

adh79 — Tue, 24 Sep 2024 11:13:48 GMT

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

Re: Counting New and Exited IDs Between Months

Kushal_Chawda — Tue, 24 Sep 2024 14:58:09 GMT

@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;

Re: Counting New and Exited IDs Between Months

adh79 — Wed, 25 Sep 2024 06:58:07 GMT

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!