Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
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

Highlighted
Contributor III
Contributor III

Thanks a Lot!!!!!!

It Worked.

Highlighted
Contributor III
Contributor III

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

Highlighted
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.

Highlighted
Contributor III
Contributor III

Thanks again swuehl