Ageing of employees according to Month

[yashdeep]

Hi everyone,

I have Following Data.

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

Emp code	Month
1	Jan 2015
2	Jan 2015
2	Feb 2015
3	Feb 2015
2	Mar 2015
3	Mar 2015
4	Mar 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

Thanks in advance.

[swuehl · Accepted solution]

Maybe like this

INPUT:
LOAD [Emp code], 
     Date#(Month, 'MMM YYYY') as Month
FROM
[https://community.qlik.com/thread/205836]
(html, codepage is 1252, embedded labels, table is @1);

RESULT:
LOAD [Emp code],
           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;

[yashdeep]

Thanks a Lot!!!!!!

It Worked.

[yashdeep]

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

Thanks in advance

[swuehl]

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.

[yashdeep]

Thanks again swuehl