Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
yashdeep
Contributor III
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
swuehl
MVP
MVP

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;

View solution in original post

5 Replies
swuehl
MVP
MVP

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

Thanks a Lot!!!!!!

It Worked.

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

Thanks in advance

swuehl
MVP
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.

yashdeep
Contributor III
Contributor III
Author

Thanks again swuehl