Entry & Exit between 2 dates using set analysis

[#1]

I have a field snapshot_date

and two variables vMax_date and vMin_date

in HR application, I am calculating People who Leaves and the people who joined between these two dates.

count({$<snapshot_date={$(#vMax_date)}-{$(#vMin_date)}>} employee_id)

but it's not giving right value.

Thanks in advance.

[colinh]

Hi Shubhasheesh

Try count({$<snapshot_date={">=$(#vMin_date)<={$(#vMax_date)"}>} employee_id)

That should give you a count of all employee_ids where snapshot_date is between those two dates, if that is what you are looking for.

[Miguel_Angel_Baeyens]

Hello,

My first guess was joining tables with intervalmatch, but this is not an option since it seems you have those date ranges in variables, not tables.

Then, if I get you right, you need to know those IDs that are between both dates, which means that snapshot_date should be less than or equal to vMax_date and greater than or equal to vMin_date. If that assumption is correct, then the expression should look like

count({$< snapshot_date = {'>=$(#vMin_date)<=$(#vMax_date)'}>} employee_id)

Hope this helps

[#4]

Solution not working.

I have attached the qvw but only two snapshot dates are there after data reduce. I have scrambled for privacy purpose.

One more thing. the above solution returns all the available candidate on vMax_date but i want only those who joined.

For example if we choose 7/12/2010 and 14/12/2010 between which 10 candidates joined and 3 left. So i need those 10 and 3 candidates, their names.

But currently it is showing 7 that means 10 - 3 is happening. Check the file and

thanks in advance

[#5]

Nice Example !!!

[Miguel_Angel_Baeyens]

I'm afraid I'm missing something. What is snapshot_date for? Is that the joining date? It's not clear in the script. How do you distinguish bewteen joins and leaves? As the data is scrambled (which is ok for me) it would be nice if you told us which charts are your drawing or you have issues to draw.

Regards.

[#7]

Sorry I have not explained it earlier.

Snapshot date is like 7 days period after which the client want to see report that means in 2 months there will be 8 ( in general ) snapshot dates.

For trends this 2 months is taken.

If you see in data model there is a synthetic key which we have to leave intentionally, and yes we have use snapshot date as joining Master and PR tables ( along with other key fields ).

In Entry and Exit Sheet, two charts in Red and Green shows Exit and Entry / New Joinee candidates.

Although I have solved it in different way, let me share it now.

The problem with these two charts were they are showing exact names of exit and entry candidates but total at top was wrong because the condition used in expression was not proper.

For example I have used ---- if total no of entry candidates - total no of exit candidates > 0 then they are entry candidates.

same way total no of exit candidates - total no of entry candidates > 0 then they are exit candidates.

Both gives consolidated figures for each location, thats OK if you want to see net change in headcount. But client want to see exact names, in this case issue arrises because those locations where net change in headcount is -ve they will not come in entry and vice versa. However assume if location A has 5 exits and 3 entries then this location must be shown in both charts with exact names.

Well, I have solved it by using Straight table chart in place of Pivot table chart because straight table has an option of counting rows, which solves the issue. Ofcourse I have to make users to select one location at a time to see total along with the names for that location.

Regards,

Shubhu