topic Re: Comparison of current and previous month in App Development

Comparison of current and previous month

MMK — Wed, 22 Oct 2025 13:05:22 GMT

Hi All,

I have a requirement where need to show the count of Leavers(Inactive users).
We have 12 months of data and have a 'Month' filter, in which by selecting a particular month, we have to compare 'Group' for previous and current month

Active - user that is present in the group in the previous and current month
Leaver - user that is present in the group in the previous month and not present in current month.

Re: Comparison of current and previous month

Daniel_Castella — Tue, 11 Nov 2025 15:57:32 GMT

Hi MMK

I’m not sure if this is the fanciest code to do it, but it works.

What I do is creating a new field called Status, so each of the lines in your sample are ‘Active’. Then, I add a new row with the Status as ‘Leaver’ for the month where a user has left. In this way, in front end you just need to count the users with Status = ‘Leaver’ to know how many left on the selected month.

Let me know if it works for you.

LOAD

"UserId",

UserId&Year("Date")&num(month("Date"),'00') as Key1,

Name,

"Group",

"Date",

Year("Date")&num(month("Date"),'00') as YearMonth,

'Active' as Status

FROM [lib://DataFiles/Sample data.xlsx]

(ooxml, embedded labels, table is Sheet1);

TempMax:

LOAD YearMonth

RESIDENT A

ORDER BY YearMonth DESC;

LET vMaxMonth = Peek('YearMonth', 0, 'TempMax');

DROP Table TempMax;

LOAD

UserId&Year(addmonths("Date",+1))&num(month(addmonths("Date",+1)),'00') as Key2,

"UserId",

Name,

"Group",

Year(addmonths("Date",+1))&num(month(addmonths("Date",+1)),'00') as YearMonth

RESIDENT A

WHERE Year(addmonths("Date",+1))&num(month(addmonths("Date",+1)),'00')<='$(vMaxMonth)'

;

OUTER JOIN(A)

LOAD

Key2 as Key1,

'Leaver' as Status,

"UserId",

Name,

"Group",

YearMonth

RESIDENT B

WHERE not exists (Key1, Key2);

DROP Table B;

NoConcatenate

Load

"UserId",

Name,

"Group",

"Date",

Month(MakeDate(Left(YearMonth,4),Right(YearMonth,2)))&' '&Left(YearMonth,4) as Month,

Status

RESIDENT A;

DROP Table A;

Kind Regards

Daniel

Re: Comparison of current and previous month

brunobertels — Thu, 23 Oct 2025 11:21:26 GMT

may be this link will help :

https://community.qlik.com/t5/QlikView-App-Dev/Joiners-and-Leavers-of-the-Month/td-p/918482

Re: Comparison of current and previous month

Daniel_Castella — Thu, 23 Oct 2025 11:40:31 GMT

*Correction: The where clause in the B table needs to be

WHERE Year(addmonths("Date",+1))&num(month(addmonths("Date",+1)),'00')<='$(vMaxMonth)'

Otherwise it will not work for other months outside the MaxMonth. This scenario was not set in the data sample, for this reason I did not saw it the first time. With the <= should work for Leavers that left in October, for example.

Kind Regards

Daniel

Re: Comparison of current and previous month

stevedark — Fri, 24 Oct 2025 14:24:38 GMT

Hi @MMK

This feels like something that Set Analysis will be able to do well.

Previous month would be something like:

count(DISTINCT {<Month*={'$(=date(addmonths(max(Month),-1),'MMM YYYY'))'}>}UserId)

So you are simply getting everyone who is in the data as at the month before the latest month in the selection.

For the leavers you will need that as a criteria and then add to it a criteria to exclude any users that appear in the current month:

count(DISTINCT {<Month*={'$(=date(addmonths(max(Month),-1),'MMM YYYY'))'},UserId=E({<Month={'$(=date(max(Month),'MMM YYYY'))'}>}UserId)>}UserId)

With the following load script:

LOAD

UserId,

Date(Date#(Month, 'MMM YYYY'), 'MMM YYYY') as Month

INLINE [

UserId,Month

1,Aug 2025

1,Sep 2025

1,Oct 2025

2,Sep 2025

2,Oct 2025

3,Oct 2025

4,Aug 2025

4,Sep 2025

];

You get a previous month and current month table that looks like this:

UserId	Last Month	Leavers
1	1	0
2	1	0
4	1	1

1,2 and 4 are all there in September, but 4 is not in October.

Attached is the QVF I used to ensure that I got my set analysis syntax right!

Hope that helps.

Steve

Re: Comparison of current and previous month

stevedark — Fri, 24 Oct 2025 14:29:12 GMT

Just seen the NULL values in the Group column in your data, rather than users just disappearing from the data. Adding to the Exclude set analysis should deal with that:

count(DISTINCT {<Month*={'$(=date(addmonths(max(Month),-1),'MMM YYYY'))'},UserId=E({<Month={'$(=date(max(Month),'MMM YYYY'))'},Group-={'NULL'}>}UserId)>}UserId)

Steve