Announcements
cancel
Showing results for
Did you mean:
Contributor III

## 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

1 Solution

Accepted Solutions
MVP

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;

5 Replies
MVP

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;

Contributor III
Author

Thanks a Lot!!!!!!

It Worked.

Contributor III
Author

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

MVP

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.

Contributor III
Author

Thanks again swuehl

Community Browser