Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
DS14
Partner - Contributor III
Partner - Contributor III

Average head count of Employees

Hi Experts,

I have to calculate Average head count of employees. And the logic for calculating it is (Opening Head count+ClosingHead count)/2.

And the data I have i.e Monthwise. As per below format.

Personnel areaMar-18 No. of EmpApr-18 No. of EmpMay-18 No. of EmpJun-18 No. of EmpJul-18 No. of EmpAug-18 No. of EmpSep-18 No. of EmpOct-18 No. of EmpNov-18 No. of EmpDec-18 No. of EmpJan-19 No. of EmpFeb-19 No. of EmpMar-19 No. of Emp
              
PA0028303030303234363638363434
PA0178787880828484848284106108110
PA02318258264262284290292302302306304310308
PA0382828282889292828080808080
PA050565656585858585858585858
PA0664646466666666666868969692
PA0758585858605658585858585858
PA0876767678747678808282828282
PA09418426420418446448438432428424420420420
PA0X262420202016121212121044
 1148115211481150120812181212121012061210125012501246

 

Now I have to calculate  Average head count on Month level and Year Level.

But on the month level the logic will work as  Previous Month closing will be current month opening. And will be added with current month closing and divided by 2.

But on the year level The logic will work as Previous Year closing + Current Year closing. I am attaching the dummy data too.

Note: The same logic should function for both with month level and year level.

Please suggest. Help Would be appreciated.

Thanks in Advance.

Labels (5)
4 Replies
HirisH_V7
Master
Master

Hi check out this code in Script,

Temp:
CrossTable(Date, Head_Count)
LOAD [Personnel area], [Mar-18 No. of Emp], [Apr-18 No. of Emp], [May-18 No. of Emp], 
[Jun-18 No. of Emp], [Jul-18 No. of Emp], [Aug-18 No. of Emp], [Sep-18 No. of Emp], 
[Oct-18 No. of Emp], [Nov-18 No. of Emp], [Dec-18 No. of Emp], [Jan-19 No. of Emp], 
[Feb-19 No. of Emp], [Mar-19 No. of Emp]
FROM [Monthwise Headcount.xlsx] (ooxml, embedded labels, table is Monthwise) where [Personnel area]<>null();


Data:
NoConcatenate
load 
[Personnel area],
Head_Count,
MonthName(Date#(SubField(Date,' ',1),'MMM-YY') )as Year_Month ,
Year(Date#(SubField(Date,' ',1),'MMM-YY')) as Year
Resident Temp order by Date;
DROP Table Temp;

Then in UI For Month level,

If(Year_Month="vMinMonth",
Sum(Head_Count),
(Sum(Head_Count)+Above(Sum(Head_Count)))/2)

Year level,

If(Year='$(=vMinYear)',
Sum(Head_Count),
(Sum(Head_Count)+Above(Sum(Head_Count)))/2)

 

HTH,

Hirish v 

PFA app for ref.

 

HirisH
“Aspire to Inspire before we Expire!”
Vengatesh
Partner - Creator
Partner - Creator

Can you give Desired output that you're looking for the above data.

You Know What To Do.
DS14
Partner - Contributor III
Partner - Contributor III
Author

Hi hirishv7 ,

Thanks for the quick response. But the output I required is not matching with the result you have provided.

Let say These are the Headcount monthwise:

ClosingMonthend
Mar-18 No. of Emp640
Apr-18 No. of Emp641
May-18 No. of Emp638
Jun-18 No. of Emp640
Jul-18 No. of Emp675
Aug-18 No. of Emp681
Sep-18 No. of Emp679
Oct-18 No. of Emp678
Nov-18 No. of Emp677
Dec-18 No. of Emp679
Jan-19 No. of Emp698
Feb-19 No. of Emp699
Mar-19 No. of Emp698

 

As per the logic ((Opening headcount + Closing headcount)/2) Now  the First thing I required Opening Month Headcount.

And the opening month headcount will be previous month closing.

Let say for example I have to find the Avg Headcount for October Month.

Then the opening Headcount for the October month will be September closing i.e 679  and

Closing Headcount for the October month will be 678.

Now as per the logic ((Opening headcount + Closing headcount)/2)

October Headcount = ((679  + 678)/2) = ((1357)/2) = 678.5  This should work for month level.

For year level  It will be ((closing Headcount of Previous year + closing Headcount of Current year)/2)

For example

The Opening Headcount for current fiscal year will closing Headcount of Previous Fiscal year  that is  March headcount  i.e = 640

And the

closing Headcount of Current Fiscal year is March headcount  i.e = 698

Now as per the logic ((Opening headcount + Closing headcount)/2)

October Headcount = ((640+ 698)/2) = ((1338)/2) = 669  This should work for Year level.

I Hope now the requirement is quite clear.

Looking forward to positive response from our side.

Thanks

 

HirisH_V7
Master
Master

For Month level use this expression,

=rangeavg(above(sum(Head_Count),0,2))

For Year level use this expression,

=rangeavg(above(
sum({<Year_Month={'$(=Monthname(min({<Year={"$(=max(Year)-1)"}>}Year_Month)))'}>}Head_Count) +
sum({<Year_Month={'$(=Monthname(max(Year_Month)))'}>}Head_Count)
,0,2))

HTH

PFA App for ref.

HirisH
“Aspire to Inspire before we Expire!”