This website is not affiliated with, sponsored by, or approved by SAP AG.

Calculate Median, Percentile, Quantile with Query

Business Warehouse

Moderators: Snowy, thx4allthefish

Calculate Median, Percentile, Quantile with Query

Postby GRK » Fri Dec 16, 2005 3:47 am

Hi SAP Fans,

Is there a way to calculate a Quantile (or something like median, percentile etc.) with standard query elements?

Certainly VBMacro and/or web table interface should possible, but this should only be the last choice.

Median has bee asked some time ago but without a solution, see below
http://www.sapfans.com/forums/viewtopic.php?t=114500&highlight=median
http://www.sapfans.com/forums/viewtopic.php?t=52905&highlight=median

GRK
GRK
 
Posts: 74
Joined: Fri Apr 16, 2004 2:23 am

Re: Calculate Median, Percentile, Quantile with Query

Postby tesla21 » Wed May 19, 2010 9:52 am

I'm looking at calculating the quantile also. Does anyone know if there is a chart that exists for this one? Will be basically using it to calculate limits in a shewhart control chart.
tesla21
 
Posts: 6
Joined: Tue May 11, 2010 1:17 pm

Re: Calculate Median, Percentile, Quantile with Query

Postby Robert Williams » Tue Jun 29, 2010 11:35 pm

A crude way to get some way towards it...

Add a key figure for Facts -- i.e. hard code the value '1' in each row of your fact table, and include it in your query alongside the Data in your sample. Sort your sample by ascending Data size and express the number of Facts as a cumulative percentage of the total, which lets you see roughly where various percentiles fall.

Code: Select all
Data   Facts   Cumulative %
3   1   1   2.6%
4   1   2   5.1%
5   1   3   7.7%
6   1   4   10.3%
7   1   5   12.8%
8   1   6   15.4%
10   1   7   17.9%
11   1   8   20.5%
12   1   9   23.1%
15   1   10   25.6%
15   1   11   28.2%
20   1   12   30.8%
27   1   13   33.3%
29   1   14   35.9%
30   1   15   38.5%
30   1   16   41.0%
33   1   17   43.6%
35   1   18   46.2%
42   1   19   48.7%
45   1   20   51.3%
50   1   21   53.8%
55   1   22   56.4%
60   1   23   59.0%
67   1   24   61.5%
72   1   25   64.1%
76   1   26   66.7%
76   1   27   69.2%
78   1   28   71.8%
79   1   29   74.4%
80   1   30   76.9%
82   1   31   79.5%
86   1   32   82.1%
88   1   33   84.6%
91   1   34   87.2%
92   1   35   89.7%
92   1   36   92.3%
95   1   37   94.9%
96   1   38   97.4%
96   1   39   100.0%
Robert Williams
 
Posts: 428
Joined: Tue Nov 05, 2002 6:37 pm
Location: Sydney, Australia


Return to Business Warehouse

Who is online

Users browsing this forum: No registered users and 4 guests





loading...


This website is not affiliated with, sponsored by, or approved by SAP AG.