Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 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 |
PA00 | 28 | 30 | 30 | 30 | 30 | 32 | 34 | 36 | 36 | 38 | 36 | 34 | 34 |
PA01 | 78 | 78 | 78 | 80 | 82 | 84 | 84 | 84 | 82 | 84 | 106 | 108 | 110 |
PA02 | 318 | 258 | 264 | 262 | 284 | 290 | 292 | 302 | 302 | 306 | 304 | 310 | 308 |
PA03 | 82 | 82 | 82 | 82 | 88 | 92 | 92 | 82 | 80 | 80 | 80 | 80 | 80 |
PA05 | 0 | 56 | 56 | 56 | 58 | 58 | 58 | 58 | 58 | 58 | 58 | 58 | 58 |
PA06 | 64 | 64 | 64 | 66 | 66 | 66 | 66 | 66 | 68 | 68 | 96 | 96 | 92 |
PA07 | 58 | 58 | 58 | 58 | 60 | 56 | 58 | 58 | 58 | 58 | 58 | 58 | 58 |
PA08 | 76 | 76 | 76 | 78 | 74 | 76 | 78 | 80 | 82 | 82 | 82 | 82 | 82 |
PA09 | 418 | 426 | 420 | 418 | 446 | 448 | 438 | 432 | 428 | 424 | 420 | 420 | 420 |
PA0X | 26 | 24 | 20 | 20 | 20 | 16 | 12 | 12 | 12 | 12 | 10 | 4 | 4 |
1148 | 1152 | 1148 | 1150 | 1208 | 1218 | 1212 | 1210 | 1206 | 1210 | 1250 | 1250 | 1246 |
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.
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.
Can you give Desired output that you're looking for the above data.
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:
Closing | Monthend |
Mar-18 No. of Emp | 640 |
Apr-18 No. of Emp | 641 |
May-18 No. of Emp | 638 |
Jun-18 No. of Emp | 640 |
Jul-18 No. of Emp | 675 |
Aug-18 No. of Emp | 681 |
Sep-18 No. of Emp | 679 |
Oct-18 No. of Emp | 678 |
Nov-18 No. of Emp | 677 |
Dec-18 No. of Emp | 679 |
Jan-19 No. of Emp | 698 |
Feb-19 No. of Emp | 699 |
Mar-19 No. of Emp | 698 |
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
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.