topic Ageing of employees according to Month in QlikView

Ageing of employees according to Month

yashdeep — Wed, 17 Feb 2016 06:43:19 GMT

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.

Re: Ageing of employees according to Month

swuehl — Thu, 18 Feb 2016 23:30:27 GMT

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;

Re: Ageing of employees according to Month

yashdeep — Fri, 19 Feb 2016 08:09:18 GMT

Thanks a Lot!!!!!!

It Worked.

Re: Ageing of employees according to Month

yashdeep — Tue, 23 Feb 2016 10:37:04 GMT

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

Re: Ageing of employees according to Month

swuehl — Tue, 23 Feb 2016 20:19:14 GMT

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

yashdeep — Thu, 25 Feb 2016 12:32:24 GMT

Thanks again swuehl