Comparison of current and previous month

[MMK]

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.

[Daniel_Castella · Accepted solution]

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.

A:

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;

B:

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;

C:

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

[brunobertels]

hi 

may be this link will help : 

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

[Daniel_Castella]

*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

[stevedark]

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

[stevedark]

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