topic Re: Count all the Employees till previous month in each month Column in QlikView

Count all the Employees till previous month in each month Column

Fri, 11 Oct 2013 15:49:52 GMT

HI All,

Can you please advise me on my bellow requirement?

1. Number of Enrollees At Beginning of The Month: - if I do not select any year from selection box, I should get latest year data (2013 as of now), and in Jan month Column I should get all the members who joined till Dec -31-2012, in Feb month column I should get all the members who joined till Jan -31-2013 and so on. Once I click on 2012 in Jan month Column I should get all the members who joined till Dec -31-2011, in Feb month column I should get all the members who joined till Jan -31-2012 and so on

2. Number of Enrollees at End of Current Month: - if I do not select any year from selection box, I should get latest year data (2013 as of now), and in Jan month Column I should get all the members who joined till Jan -31-2013, in Feb month column I should get all the members who joined till Feb -28-2013 and so on. Once I click on 2012 in Jan month Column I should get all the members who joined till Jan -31-2013, in Feb month column I should get all the members who joined till Feb -28-2012 and so on

You all can view the attached qvw for better understanding of my requirement.

Re: Count all the Employees till previous month in each month Column

Gysbert_Wassenaar — Fri, 11 Oct 2013 18:30:22 GMT

I think you need as AsOf table. See attached example.

Re: Count all the Employees till previous month in each month Column

Mon, 14 Oct 2013 15:17:14 GMT

Hi Wassenaar,

Thanks for your quick reply; this helps me a lot to get exactly what I need. This was for only one section, but in the same data model I need to include other subject areas information like Employee Communication with employer via email etc... , Employee goal what he/she decided? In my FACT_MEMBERS qvd’s I have a separate column for those subject area dates. In my DATELINK qvd’s Type is the column which determines that date pertains to which Subject Area, for me 1 is for Employee Join date, 2 is for Employee communication(email Send) date, 3 is for Employee goals, 5 are for Employee Termination date.

I tried to accommodate other subject area in Asof link table, but not able to get the data.

Below is requirement for other rows:-

3. Number Of Employees Terminated in Current month: - if I do not select any year from selection box, I should get latest year data (2013 as of now), and in Jan month Column I should get all the members whose Termination date is in Jan – 2013, in Feb month Column I should get all the members whose Termination date is in Feb – 2013,

4. Number of Email an Employee send: - if I do not select any year from selection box, I should get latest year data (2013 as of now), let’s say in Jan month Column I should get all the emails count which sent in Jan – 2013, in Feb month Column I should get all the emails count which sent in Feb – 2013, E_RNK is the column which tells us the weather that records is for Email or not, if it’s 1 that pertains to Email, 0 means that records is related to other subject area.

You can use old qvw for any reference.

Thank you so much for your help........