cancel
Showing results for 
Search instead for 
Did you mean: 

Is percentile calculation supported by QD or WAD?

Former Member
0 Kudos

Hi!

Customer does not have access to Business Objects, Crystal Reports or similar but requires percentiles to be calculated at runtime.

Customer wants 10th, 25th, 50th, 75th and 90th percentile of the employees' individual wages to be presented for whatever combination or drill-down of characteristics are selected in report. Eg first display is organizational units as a hierarchy, each unit should have the percentiles displayed and calculated on the wages of the employees for that unit. Also, minimum wage for each unit and total sum for wages for each unit should be presented.

If user adds Gender from free characteristics, the percentile values should be re-calculated so that they are based on the employees' wages for each gender under each unit.

Is this at all possible?

Query Designer does not provide any options unless we predefine which characteristics we want to use and sort and index the data accordingly. This is not an option since user will analyse many scenarios and does not know beforehand which scenarios are applicable.

Web Application Designer can offer median value (50th percentile) via context menu but only based on the subtotals and totals presented in query (eg total wage for organizational unit, not employees' individual wages).

Only option as we see it now is to try to create an enhancement of context menu in WAD and use a customer exit to do the calculation and then send result back to query. But is this a solution? Will it even work? Can this work dynamically or must user make selection on menu for each re-calculation of the values? Will it damage performance?

There is very little information about percentile calculations on forums or SAP help, is this not supported at all if customer does not use BO or similar?

Please advice on solution or information about whether or not this is supported by BI.

(links to info regarding enhancement and customer exit

http://help.sap.com/saphelp_nw04/helpdata/en/a4/7f853b175d214ee10000000a11402f/content.htm

http://help.sap.com/saphelp_nw70/helpdata/en/47/a3d30269421599e10000000a42189c/content.htm)

(System information: SAP Netweaver 7.0 EHP 1, suppport pack 😎

Accepted Solutions (0)

Answers (2)

Answers (2)

former_member188080
Active Contributor
0 Kudos

Hi,

Just see if you can do this in workbook either with the help of pivot table or macros or simple excel functionality based on query output result.

Thanks and regards

Kiran

Former Member
0 Kudos

Hi Kiran,

thanks for your reply. However, the customer does not use Bex but accesses report via template in portal and does not want to save the report to Excel since they would need to do so with all employees listed in report. This would mean tens of thousands of rows upon which the calculations would be performed.

Please let me know if you have other suggestions.

Kind regards

Carin

former_member185132
Active Contributor
0 Kudos

Hi,

Percentiles should be achievable using the Condition functionality in BEx QD. In Conditions, you can specify that you want to restrict the output the rows that are in the Top N%.

When you create a condition, you can select the set of characteristics it applies. If you select the option "All characteristics in the drill down independently" BEx will automatically recalculate the percentiles.

Regards,

Suhas

Former Member
0 Kudos

Hi Suhas,

thanks for your kind reply.

However, this does not solve the issue. I can take out the top 10% of values and must then select the minimum value of these top 10% in order to receive the actual 90th percentile. Also: I believe this condition will apply to the entire query which means I can not at the same time get the bottom 10% and the maximum value for the bottom 10% in order to also display the 10th percentile. Then I must set the condition another 3 times to receive the 25th, 50th and 75th percentile.

How could this be achieved?

And does not this condition use only the presented totals (eg value for org unit as displayed) rather than the lowest granular data (value for each employee for each org unit)?

Looking forward to your reply.

Best regards

Carin

former_member185132
Active Contributor
0 Kudos

Well, you're right. There is an amount of manual action required. And the condition does indeed look at the characteristics in the drill-down - so if employee no in not in the drill down, then the lowest granular data will not be taken into account.

If you want to avoid that, you'll need a different approach. You could create a vanilla (without condition query) and write an APD to pull data from that query. The APD would then contain a routine to perform this calculation of the 10th percentile and that could be sent into a DSO and thereafter to a Cube. The same APD for 25th, 50th percentile etc. Eventually the target cube, for each Org Unit, would have one record for the 10th percentile, one for the 25th etc. The query could be built on this cube and could pick up the percentile required by the user.

The trouble with this design is that you'll have to think of all possible combinations of characteristics (Org Unit, Gender etc) and use the APD to calculate them. But I can't think of any other way to handle it either.

Regards,

Suhas

Former Member
0 Kudos

Hi Suhas,

thanks again. It is a good suggestion, but as you point out we have to consider every possible combination of data which is not a good solution for us. (There would be probably 50+ combinations.)

Do you have any experience from enhancing the context menu in WAD? And using a customer exit to create data for the report display?

Kind regards,

Carin