5 Replies Latest reply: Feb 25, 2016 7:32 AM by yashdeep chauhan

# Ageing of employees according to Month

Hi everyone,

I have Following Data.

Fields=Emp Code,Month(Month in which Employee won't be having any Project)

Emp codeMonth
1Jan 2015
2Jan 2015
2Feb 2015
3Feb 2015
2Mar 2015
3Mar 2015
4Mar 2015

I want Report in below format:

• This report should give the number  of months the employee is on bench.
•     I have to take two dimensions

1.Ageing= No. of months in which he is not having any project

2.Month= Count of Employees not having project in That Month

• I have to take count of Employees

 Month   (Ageing) Mar-15 Feb-15 Jan-15 1 1 1 2 2 1 1 3 1

• ###### Re: Ageing of employees according to Month

Maybe like this

```
INPUT:
Date#(Month, 'MMM YYYY') as Month
FROM
(html, codepage is 1252, embedded labels, table is @1);

RESULT:
Month,
If([Emp code] = Previous([Emp code]) and AddMonths(Month,-1)=Previous(Month),Rangesum(1,Peek(Ageing)) ,1) as Ageing
RESIDENT INPUT
ORDER BY [Emp code], Month asc;

DROP TABLE INPUT;
```
• ###### Re: Ageing of employees according to Month

Thanks a Lot!!!!!!

It Worked.

• ###### Re: Ageing of employees according to Month

For the above data I have to calculate average ageing for particular Month.

ex.

For Mar 15,

(1*1    +    1*2   +   1*3  )  /  (  1  +  1  +  1)  = 2

For Feb 15,

(1*1  +  1*2 )/(1  +  1)  = 1.5

For Jan 15

(1*1)  /  1 =1

• ###### Re: Ageing of employees according to Month

Try an expression like

=Avg(Total<Month> Aggr( Count(DISTINCT [Emp code])*Ageing, Month, Ageing))

in the pivot table chart or a chart with dimension Month.

• ###### Re: Ageing of employees according to Month

Thanks again swuehl