Joiners and Leavers of the Month

[#1]

Hi Experts,

I have a requirement to find the Joiners and leavers of the Month.Please find the scenario below.

1)There is a list of clients which are updated monthly and the clients can be present in one month and not present in another month.

2)For whatever month is selected in the list box , i need to get the joiners of the month and levers of the month.

3)Joiners = The clients which are not present in the previous month but present in the current month( selected Month).

4)Leavers= The clients present in the previous month but not present in the current month(selected Month)

Can you please let me know how can i achieve this ?

[sunny_talwar]

May be this:

Joiners:

=Count({<Clients = {"=Count({<MonthYear = {"$(=MonthName(Max(MontYear)))"}>}Clients) > 0)"}*{"=Count({<MonthYear = {"$(=MonthName(AddMonths(Max(MontYear), -1)))"}>}Clients) = 0"}>} Clients)

Leavers:

=Count({<Clients = {"=Count({<MonthYear = {"$(=MonthName(Max(MontYear)))"}>}Clients) = 0)"}*{"=Count({<MonthYear = {"$(=MonthName(AddMonths(Max(MontYear), -1)))"}>}Clients) > 0"}>} Clients)

[#3]

Thanks for the reply,How can i get the list of leavers and Joiners as i need the details of the joiners and leavers like their name, id and other information?

[Anonymous · Accepted solution]

I have a similiar Task which I solved by this expresion:

=Count( DISTINCT {$ < contractnr= P({<yourdate={'$(=vDate2)'}>} contractnr)-
P({<yourdate={'$(=vDate1)'}>} contractnr)>} contractnr)

I shortened the Expression. I hope that the important things are still there

This Expression calculates the Joiners

What it does:

It counts the possible data for the first date(the new month) and  subtracts those data which existed in second date (the previous month)

For Leavers just Change the two datefields. Try first to get the exrpession for Joiners working

[sunny_talwar]

May be add Name, ID and all other information you need for them as dimension and add this as your expression for Joiners

=Only({<Clients = {"=Count({<MonthYear = {"$(=MonthName(Max(MontYear)))"}>}Clients) > 0)"}*{"=Count({<MonthYear = {"$(=MonthName(AddMonths(Max(MontYear), -1)))"}>}Clients) = 0"}>} Clients)

Do the same for leavers in another chart with the following expressions:

=Only({<Clients = {"=Count({<MonthYear = {"$(=MonthName(Max(MontYear)))"}>}Clients) = 0)"}*{"=Count({<MonthYear = {"$(=MonthName(AddMonths(Max(MontYear), -1)))"}>}Clients) > 0"}>} Clients)

[Gabriel]

Hi,

I think you're best having a flag created in the script to identify Joiners, Leavers which will optimize your expression and UI.

If you post a sample data community will help with flags creation

[Anonymous]

PFA

Don't know how this works on ur dataset.

[#8]

Thanks Everyone for replying, it solved my problem.

[#9]

Hi Rudolf,

Thanks for the information.The joiners is working perfectly .But for the leavers when i change the dates, it is working when i hard code the values for the dates but it is not working with the change in the MonthYear selection.

Can you please let me know how can we achieve this for the Leavers?

[Anonymous]

Hi,

I really have the same Expression, only the Dates are changed. Both are Input variables. I know that I too struggled a lot and in the beginning with hard coded values, I got it running earlier.

But in my current app the following Expression is working (some more set Analysis, but the most important you see below). can you send a sampkle qvw? maybe I cancheck if I find something. Sometimes Qlikview is not really straightforward in development. Try, try and if it's not working try other way.

=Count( DISTINCT {$ < contractnr= P({<yourdate={'$(=vDate1)'}>} contractnr)-
P({<yourdate={'$(=vDate2)'}>} contractnr)>} contractnr)

my example :left is first month (canbe defined), next Expression are joiners (in your case), thirs are leavers and last Expression is new month (can be defined as well)