Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

yashdeep
New 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-15Feb-15Jan-15
1112
211
31

Thanks in advance.

1 Solution

Accepted Solutions
MVP
MVP

Re: Ageing of employees according to Month

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;

5 Replies
MVP
MVP

Re: Ageing of employees according to Month

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
New Contributor III

Re: Ageing of employees according to Month

Thanks a Lot!!!!!!

It Worked.

yashdeep
New Contributor III

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

Thanks in advance

MVP
MVP

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.

yashdeep
New Contributor III

Re: Ageing of employees according to Month

Thanks again swuehl

Community Browser