Focal Point
Percentage Aggregation - YTD Turnover

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/1461084022/m/1187092976

November 10, 2015, 02:45 PM
Mike Adamson
Percentage Aggregation - YTD Turnover
I'm new to PMF, so I'm hoping I'm missing something obvious.

I'm using a percentage aggregation to calculate employee turnover ([Terminations] / [Avg Monthly Headcount]), which works fine for each month. For the YTD calculation, PMF sums all of the terminations, which is good, but it also sums all of the monthly headcounts, which I don't want. I need the denominator to be an average of the monthly results rather than a sum. Is this possible?


Edition: WebFOCUS
Product Release: 8.0
Service Pack: 08
Build Version: branch8008
Build/GEN Number: 27
Build/GEN Date: Wed May 07 20:04:22 EDT 2014
Application Server: Apache Tomcat/7.0.33
November 12, 2015, 09:24 AM
EricH
Mike - Perhaps I'm misunderstanding your requirement, but it looks like this value will increase with each month.

Month   Turnover  Headcount   Avg Head Count    Turnover
-----   --------  ---------    -------------    -----------------
Jan            3        100              100       3 / 100 =  3.0%
Feb           10        110              105      13 / 105 = 12.4%
Mar            5        105              105      18 / 105 = 17.1%
etc
  


It this what you're looking for?
November 12, 2015, 10:16 AM
Mike Adamson
Correct. I need the total terminations in a given period divided by the average number of employees in that same period.

I have other measures with similar requirements (ex. Sum of sales in any period / Average days in the same period), so solving this will be a huge help!


Edition: WebFOCUS
Product Release: 8.0
Service Pack: 08
Build Version: branch8008
Build/GEN Number: 27
Build/GEN Date: Wed May 07 20:04:22 EDT 2014
Application Server: Apache Tomcat/7.0.33
November 16, 2015, 11:58 AM
J.L. Hinds
Look up recomputed or summarize I think.


WebFOCUS 7.6
Windows, All Outputs
November 30, 2015, 11:13 AM
Mike Adamson
J.L., I'm pretty new to WebFOCUS. Do you mind elaborating on your comment?
December 01, 2015, 11:13 AM
J.L. Hinds
If I'm understanding the question

TABLE FILE CAR
SUM
DEALER_COST
RETAIL_COST
COMPUTE NEWVAR/D10.1%=(RETAIL_COST-DEALER_COST)/DEALER_COST*100;
BY COUNTRY
ON TABLE SUMMARIZE
END


WebFOCUS 7.6
Windows, All Outputs
December 04, 2015, 10:43 AM
Mike Adamson
Here's what I'm looking to accomplish:

Monthly Terms | Monthly Headcount | Monthly Turnover | YTD Turnover

17 | 583 | 2.9% | 2.9%
10 | 578 | 1.7% | 4.7%
18 | 578 | 3.1% | 7.8%
23 | 579 | 4.0% | 11.7%
20 | 578 | 3.5% | 15.2%
20 | 584 | 3.4% | 18.6%
23 | 593 | 3.9% | 22.5%
34 | 603 | 5.6% | 28.2%
22 | 611 | 3.6% | 31.8%
19 | 616 | 3.1% | 34.9%

The formula for monthly is simply (Monthly Terms) / (Monthly Headcount), which works fine. The formula for YTD Turnover is SUM(Monthly Terms) / AVERAGE(Monthly Headcount), which is where I'm having the problem. PMF doesn't give me the option to use an average, so when it calculates the YTD (or any other period of time), it adds all of the terms, which is good, but also adds all of the monthly headcounts, which is bad.

This is a standard HR measure, so I can't imaging that I'm the first person that has needed this. I have several measures that need the same concept with different denominators (ex. Avg Days per Month).

All that said, remember I'm a newbie to PMF, and I should also add that I don't have a coding background. For any type of coding like was suggested above, where do I even do this in PMF?

I appreciate any help I can get. The answer I got from IBI Tech Support was "I'm afraid it is not possible. I'm sorry we have no plan to implement this feature for now and it is not available." Given that these are common measures, I refuse to give up so easily!
December 04, 2015, 12:09 PM
Bob Jude Ferrante
Mike,
Thanks for sticking to it.
We're looking into this.

thanks


Bob Jude Ferrante
Director of Business and Development
WebFOCUS Performance Management
Bob_Ferrante@ibi.com
917-339-5105

I'll take any questions about PMF - business or technical - anytime!

December 04, 2015, 12:35 PM
Bob Jude Ferrante
Hi Mike,
The only place for this to get done would be in the reporting core code in PMF because it is actually a different aggregation method - the denominator has to be averaged at reporting time, not when the data is loaded into the mart.

If we were to add this capability, it would mean you'd need to upgrade to the version of PMF that supported it.

PMF 8.1.1 is about to ship and won't have this.

The next release will be 8.1.2, sometime around the end of Jan/beginning of Feb 2016.

Would it work for you if we did it then?

Side benefit is you would get our new gorgeous dashboard system (coming out in 8.1.1) with that upgrade.

Let us know.


Bob Jude Ferrante
Director of Business and Development
WebFOCUS Performance Management
Bob_Ferrante@ibi.com
917-339-5105

I'll take any questions about PMF - business or technical - anytime!

December 09, 2015, 04:56 PM
Mike Adamson
Waiting until February isn't ideal, but it's much better than not having the capability at all, and I look forward to seeing the new dashboards.

Thanks for looking into this, Bob.


Edition: WebFOCUS
Product Release: 8.0
Service Pack: 08
Build Version: branch8008
Build/GEN Number: 27
Build/GEN Date: Wed May 07 20:04:22 EDT 2014
Application Server: Apache Tomcat/7.0.33
December 09, 2015, 05:12 PM
Bob Jude Ferrante
Hi Mike,
Sometimes it takes time to make things that really work.
We got you on the next train.
If we can get it any sooner we'll let you know.

Thanks for being a great customer.


Bob Jude Ferrante
Director of Business and Development
WebFOCUS Performance Management
Bob_Ferrante@ibi.com
917-339-5105

I'll take any questions about PMF - business or technical - anytime!