count all records with criteria

[curiousfellow]

To calculate a percentage I have to count all employees per startyear. The result should be displayed in the current year.

Nott all employees are still in the records of the current year.

I thought they could be counted by

=Count({1<startyear={$(=currentyear)}>}employee)

but then I don't get any results

In my final version I have to calculate :

number of employees left in current year/ number of employees in startyear *100

Set Analysis functions still give me a headache

[marcus_sommer]

I think it should look more like:

=Count({< currentyear = {">=startyear"} >} employee)

- Marcus

[sasiparupudi1]

try if

sum(if(startyear=currentyear,employee,0))

hth

Sasi

[stevedark]

I think that what you may be missing there is the calculation of what the current year is.  In your expression "currentyear" will have many values.  You need to find the latest with a max:

=Count({1<currentyear={$(=max(currentyear))}>}employee)

That will give a count of employees still present in the latest year.

If you have a dimension on the table of startyear then it will only count those who are still in the company who started in any given year.  If you want all current employees then you will need to add a TOTAL statement:

=Count(TOTAL {1<currentyear={$(=max(currentyear))}>}employee)

Where you have a value of left=J, do you want to exclude that from the current year count?  If so, that can be done like this:

=Count(TOTAL {1<currentyear={$(=max(currentyear))},left-={'J'}>}employee)

Hope that helps.

Steve

[curiousfellow]

None of the answers gave me the results i am looking for. Must be while I did not define correct what I am trying to achieve. My sincere excuses for that.

If you will allow me, I'll try once more :

I want to create a pivot table : !

colums : current year

columnheader : startyear

rows : number of employees in startyear

          number of employees left

          number of employees left as percentage of the number of employees in startyear

I  added an excell sheet with better data and an example of what the result should be, and a qvw with the results of the given answers.