Skip Headers

Oracle9iAS Discoverer Plus User's Guide
Version 9.0.2

Part Number A90879-02
Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

A
Discoverer calculation examples

This appendix contains the following sections:

Getting more information

For more information about Oracle functions in general, refer to the following Oracle publications:

About the examples in this chapter

The examples in the following sections use the Video Stores Tutorial supplied with Discoverer. If you do not have the Video Stores Tutorial installed, contact the Discoverer manager.

How to I create calculations?

For information about how to create calculations, see "How to create calculations". You can also find a worked example of creating a calculation in 'Exercise 6 - Adding Calculations' in the Oracle9iAS Discoverer Plus Tutorial.

About using parameters to provide dynamic input to calculations

You often use parameters to provide dynamic input to calculations. This enables other values to be entered arbitrarily for more effective analysis. In other words, to provide a different value to a calculation, you simply refresh the worksheet and enter a new value in the "Edit Parameter Values dialog".

Parameter values used in calculations are prefixed with a colon (:). For example, a parameter called Hypothetical Value would be referenced in a calculation as follows:

For more information about using parameter values in calculations, see "About using parameters to collect dynamic user input".

Simple calculation examples

The examples in this section show you how to use basic functions with Discoverer to manipulate and analyze data.

Examples:

Notes

Example: Calculate the number of rows returned by a query

This example calculates the number of rows returned by a query using the Oracle function ROWCOUNT().

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Region, City, Sales SUM 

Sort Order 

Year, Region, City 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000

Region = Central 

Calculation Name 

Rows returned 

Calculation 

ROWCOUNT 

Figure 19-1 Worksheet containing the Rows returned calculation


Text description of af_simpl.gif follows.
Text description of the illustration af_simpl.gif

The worksheet shows the number of rows returned for each city in the central region for the year 2000.

Notes

Example: Calculate a 25% increase in sales

This example calculates a 25% increase in sales.

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Region, City, Sales SUM 

Sort Order 

Year, Region, City 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000

Region = Central 

Calculation Name 

25% Increase 

Calculation 

Sales SUM * 1.25 

Figure 19-2 Worksheet containing the 25% sales increase calculation.


Text description of af_simpb.gif follows.
Text description of the illustration af_simpb.gif

The worksheet shows a 25% increase in sales for cities in the central region.

Example: Convert text to upper-case

As well as the extensive range of mathematical functions available in Discoverer, you also have access to a wide range of number and text formatting functions. This example uses a calculation to re-format City text data to upper-case

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Region, City, Sales SUM 

Sort Order 

Year, Region 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000

Region = Central 

Calculation Name 

City(Upper Case) 

Calculation 

UPPER(City) 

Figure 19-3 Worksheet containing the City(Upper Case) calculation


Text description of af_simpa.gif follows.
Text description of the illustration af_simpa.gif

The figure above shows a worksheet containing the city names for the central region converted to upper case.

Oracle8i analytic function examples

The examples in this section show you how to use the Oracle8i analytic functions with Discoverer to perform detailed data analysis.

This section contains the following topics:

Notes

Analytic function categories

Analytic functions are classified in the following categories:

Calculations and drilling into and out of data

When you use analytic functions, note that they have a precise definition which does not change as you drill, pivot, or sort the result set. For example, if you use the RANK function to assign ranks to sales figures partitioned by quarter, if you drill down to the month level, the rank still only applies to the quarter level.

About the analytic function template

When creating analytic functions in Discoverer, you can either type or paste them directly into the Calculation dialog box, or you can select them from the function list.

If you select them from the function list, you are presented with a generic Analytic Function Template that helps you define the function by telling you what information you might need to provide. Templates should be used as a guide. Because templates are designed to cover most types of usage, you will not always need to use every part of the template.

For example, when you paste a new RANK Analytic Function into a calculation box, Discoverer provides the following template:

Although you can define a complex function using both expressions (expr1 and expr2), you can often define a simple function using only the ORDER BY expression; for example:

NOTE: By default, results data is sorted in ascending order (ASC), nulls first (NULLS FIRST).

For more information about expressions used by the Analytic Function template, see "More about the Discoverer analytic function template".

Ranking function examples

About ranking

Ranking functions compute the league table position (or rank) of an item with respect to other items in an ordered list.

Examples:

Example: Assign ranks to sales figures

This example calculates the league table position (or rank) of a set of sales figures.

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Region, City, Sales SUM 

Sort Order 

Rank Sales, Year, Region 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000 

Calculation Name 

Rank Sales 

Calculation 

RANK() OVER(ORDER BY Sales SUM DESC) 

Figure 19-4 Worksheet containing the Rank Sales calculation


Text description of af_rank.gif follows.
Text description of the illustration af_rank.gif

The worksheet shows the league table position of sales figures for cities in the year 2000.

Notes

Example: Assign ranks to sales figures within region

This example calculates the league table position (or rank) of a set of sales figures within each region.

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Region, City, Sales SUM 

Sort Order 

Year, Region 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000 

Calculation Name 

Rank sales within Region 

Calculation 

RANK() OVER(PARTITION BY Year, Region ORDER BY Sales SUM DESC) 

Figure 19-5 Worksheet containing the Rank sales within Region calculation


Text description of af_rank_.gif follows.
Text description of the illustration af_rank_.gif

The worksheet shows the league table position of sales figures for cities grouped by region within year.

Notes

Example: Show the top three selling cities per region

This example calculates the league table position (or rank) of a set of sales figures and displays the top three selling cities.

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Region, City, Sales SUM 

Sort Order 

Year, Region 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000

Rank Top <= 3 

Calculation Name 

Rank Top 

Calculation 

RANK() OVER(PARTITION BY Year, Region ORDER BY Sales SUM DESC) 

Figure 19-6 Worksheet containing the Rank Top calculation used in a condition


Text description of af_ranka.gif follows.
Text description of the illustration af_ranka.gif

The worksheet shows a league table of the top three highest sales figures for each region within year.

Notes

Example: Show the top three and bottom three selling cities per region

This example calculates the league table position (or rank) of a set of sales figures and displays the top three and bottom three performing cities per region.

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Region, City, Sales SUM 

Sort Order 

Year, Region, Rank Top 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000

Rank Top <= 3 OR Rank Bottom <= 3 

Calculation Name 

Rank Top 

Calculation 

RANK() OVER(PARTITION BY Year, Region ORDER BY Sales SUM DESC) 

Additional Calculations Required 

Rank Bottom = RANK() OVER(PARTITION BY Year, Region ORDER BY Sales SUM ASC) 

Figure 19-7 Worksheet containing the Rank Top calculation used in a condition


Text description of af_rtb3.gif follows.
Text description of the illustration af_rtb3.gif

The worksheet shows a league table of the three highest and three lowest sales figures for each region within year.

Notes

Banding function examples

About banding

Banding is a type of ranking that divides a list of values in a partition into a specified number of groups called bands (also known as buckets) and assigns each value to a band.

Examples:

Two common types of banding are:

Use the GREATEST function or the CASE function to produce equi-width bands based on value.

Note: If you are using an Oracle9i database, use the WIDTH_BUCKET function to produce equi-width bands (see "Example: Producing equi-width bands using WIDTH_BUCKET").

Use the NTILE function to produce equi-height bands based on rank.

Example: Producing equi-width bands (1)

This example divides sales figures into bands according to their value (also known as equi-width bands). For more information, see "Example: Producing equi-width bands (2)".

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Region, City, Sales SUM 

Sort Order 

Year, Region 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000

Region = Central 

Calculation Name 

Sales Bands 

Calculation 

GREATEST(1,4-FLOOR((Sales SUM-Min Sales for Region)/GREATEST(1,FLOOR((Max Sales for Region-Min Sales for Region+1)/4)))) 

Additional Calculations Required 

Max Sales for Region = MAX(Sales SUM) OVER(PARTITION BY Region,Year)

Min Sales for Region = MIN(Sales SUM) OVER(PARTITION BY Region,Year) 

Figure 19-10 Worksheet containing the Sales Bands calculation


Text description of af_band_.gif follows.
Text description of the illustration af_band_.gif

The worksheet shows equi-width bands for sales figures for cities in the central region within year.

Notes

Example: Producing equi-width bands (2)

This example creates the same results as the example in "Example: Producing equi-width bands (1)", except that it uses a CASE statement rather than the GREATEST function.
Again, the example divides sales figures into bands according to their value, using a CASE function (for more information, see "Example: Producing equi-width bands (1)").

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Region, City, Sales SUM 

Sort Order 

Year, Region 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000

Region = Central 

Calculation Name 

Sales Bands 2 

Calculation 

CASE
WHEN Sales SUM < Q1 THEN 4
WHEN Sales SUM < Q2 THEN 3
WHEN Sales SUM < Q3 THEN 2
WHEN Sales SUM >= Q3 THEN 1
END 

Additional Calculations Required 

MAX Sales = MAX(Sales SUM) OVER(PARTITION BY Year)

MIN Sales = MIN(Sales SUM) OVER(PARTITION BY Year)

Range = (MAX Sales - MIN Sales)/4

Q1 = MIN Sales + Range

Q2 = MIN Sales + (Range*2)

Q3 = MAX Sales - Range 

Figure 19-11 Worksheet containing the Sales Bands 2 calculation


Text description of af_banda.gif follows.
Text description of the illustration af_banda.gif

The worksheet shows equi-width bands for sales figures for cities in the central region within year.

Notes

Example: Producing equi-height bands

This example assigns a set of sales figures into two equi-height bands.

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Region, City, Sales SUM 

Sort Order 

Year, Region 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000

Region = Central 

Calculation Name 

Sales Bands 3 

Calculation 

NTILE(2) OVER(PARTITION BY Year, Region ORDER BY Sales SUM DESC) 

Figure 19-12 Worksheet containing the Sales Bands 3 calculation


Text description of af_bandb.gif follows.
Text description of the illustration af_bandb.gif

The worksheet shows equi-height bands for sales figures for cities in the central region within year.

Notes

Windowing function examples

About windowing

Windowing functions are used to compute aggregates using values from other rows. For example, cumulative, moving, and centered aggregates.

Examples:

Two common types of windowing are:

Example: Calculate a three month moving sales average

This example uses a logical window to calculate a moving three month sales average.

Note: Moving averages are also known as rolling averages.

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Calendar Month, Sales SUM 

Sort Order 

Year, Month 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000

Region = Central 

Calculation Name 

Moving Average 

Calculation 

AVG(Sales SUM) OVER(ORDER BY "Calendar Month" RANGE INTERVAL '2' MONTH PRECEDING ) 

Figure 19-13 Worksheet containing the Moving Average calculation


Text description of af_windo.gif follows.
Text description of the illustration af_windo.gif

The worksheet shows a moving three month average for sales figures for months in the year 2000.

Notes

Example: Show the cumulative values of sales

This example uses a physical window to calculate the cumulative value of sales.

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Region, City, Sales SUM 

Sort Order 

Year, Region 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000

Region = Central 

Calculation Name 

Cumulative Total 

Calculation 

SUM(Sales SUM) OVER(PARTITION BY "Calendar Year", Region ORDER BY Sales SUM ROWS UNBOUNDED PRECEDING ) 

Figure 19-14 Worksheet containing the Cumulative Total calculation


Text description of af_cumul.gif follows.
Text description of the illustration af_cumul.gif

The worksheet shows a cumulative total for sales figures for cities in the central region.

Example: Compare sales figures across time using windowing

This example uses a logical window to calculate sales figures for previous years. This enables you to compare sales figures over different years, or compare previous years' sales figures with other values, such as spending in previous years.

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Sales SUM 

Sort Order 

N/A 

Conditions 

N/A 

Calculation Name 

Sales Last Year 

Calculation 

SUM(Sales SUM) OVER(ORDER BY "Calendar Year" RANGE BETWEEN INTERVAL '1' YEAR PRECEDING AND INTERVAL '1' YEAR PRECEDING ) 

Figure 19-15 Worksheet containing the Sales Last Year calculation


Text description of af_win3.gif follows.
Text description of the illustration af_win3.gif

For each row, the worksheet shows the sales total for the previous year.

Notes:

Reporting function examples

About reporting functions

Reporting functions are used to compute aggregates.

Examples:

Example: Calculate annual sales

This example calculates annual sales.

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Region, City, Sales SUM 

Sort Order 

Year, Region 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000 

Calculation Name 

Annual Sales 

Calculation 

SUM(Sales SUM) OVER() 

Figure 19-16 Worksheet containing the Annual Sales calculation


Text description of af_repor.gif follows.
Text description of the illustration af_repor.gif

The worksheet shows the annual sales value for cities in the year 2000.

Notes

Example: Calculate annual sales by region

This example calculates the total annual sales by region.

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Region, City, Sales SUM 

Sort Order 

Year, Region 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000 

Calculation Name 

Annual Sales by Region 

Calculation 

SUM(Sales SUM) OVER(PARTITION BY Year, Region ORDER BY Year, Region) 

.

Figure 19-17 Worksheet containing the Annual Sales by Region calculation


Text description of af_repoa.gif follows.
Text description of the illustration af_repoa.gif

The worksheet shows the annual sales total for cities, grouped by region within year.

Example: Calculate percentage of annual sales by region

This example calculates the percentage of annual sales per region for each city in each year.

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Region, City, Sales SUM 

Sort Order 

Year, Region, % of Annual Sales 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000 

Calculation Name 

% of Annual Sales 

Calculation 

Sales SUM*100/Annual Sales by Region 

Additional Calculations Required 

Annual Sales by Region = SUM(Sales SUM) OVER(PARTITION BY Year, Region ORDER BY Year, Region) 

Figure 19-18 Worksheet containing the % of Annual Sales calculation


Text description of af_repoc.gif follows.
Text description of the illustration af_repoc.gif

The worksheet shows sales as a percentage of annual sales, grouped by region within year.

Example: Calculate city sales as a percentage of total sales

This example calculates city sales as a percentage of total sales.

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Region, City, Sales SUM 

Sort Order 

Year, Region, % of Annual Sales 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000

Region = Central 

Calculation Name 

% of total Sales 

Calculation 

RATIO_TO_REPORT(Sales SUM) OVER()*100 

Figure 19-19 Worksheet containing the % of Annual Sales calculation


Text description of af_repob.gif follows.
Text description of the illustration af_repob.gif

The worksheet shows the sales value for cities as a percentage of total sales.

Notes:

LAG/LEAD function examples

About LAG/LEAD functions

LAG and LEAD functions are typically used to compare values in different time periods. For example, compare sales figures in 2000 with sales figures in 2001.

Note: You can also use windowing functions to compare values over time (see "Example: Compare sales figures across time using windowing".

Examples:

Example: Compare sales figures across time using LAG/LEAD

In this example, you want to compare monthly sales figures with sales figures for the same month in the previous year. For example, to look at how January 1999 sales compare with January 1998 sales.

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Calendar Quarter, Calendar Month, Sales SUM 

Sort Order 

Calendar Year, Calendar Quarter, Calendar Month 

Conditions 

Department = Video Sale OR Department = Video Rental 

Calculation Name 

Previous Year 

Calculation 

LAG(Sales SUM,1) OVER(PARTITION BY "Calendar Month" ORDER BY "Calendar Year" ) 

Figure 19-20 Worksheet containing the Previous Year calculation partitioned by Calendar Month


Text description of af_lagle.gif follows.
Text description of the illustration af_lagle.gif

The worksheet shows contains the calculation Previous Year, which shows for each Sales SUM amount the sales amount for one year previously. For example, the Previous Year value for January 1999 is $50889, which is the Sales SUM value for January 1998.

Notes

Example: Calculate sales growth across time

In this example, you want to calculate the percentage growth of sales across years by comparing the sales figures with sales figures for the same month in the previous year. You will do this using the comparative sales figures from example "Example: Compare sales figures across time using LAG/LEAD".

Worksheet option  Set to: 

Items 

Video Analysis Information: Calendar Year, Calendar Quarter, Calendar Month, Sales SUM 

Sort Order 

Calendar Year, Calendar Quarter, Calendar Month 

Conditions 

Department = Video Sale OR Department = Video Rental 

Calculation Name 

Growth % 

Calculation 

( Sales SUM-"Previous year" )*100/"Previous year" 

Additional Calculations Required 

LAG(Sales SUM,1) OVER(PARTITION BY "Calendar Month" ORDER BY "Calendar Year" ) 

Figure 19-22 Worksheet containing the Growth calculation


Text description of af_lagla.gif follows.
Text description of the illustration af_lagla.gif

The worksheet shows contains the calculation Growth %, which shows for each month the percentage increase in sales since the previous year. For example, the Growth % value for January 1999 is 30.40% (i.e. from $50889 to $67887).

Notes

Example: Rank sales growth

In this example, you want to create a league table of sales growth, to show which months show the highest year on year increase in sales.

You will do this using the comparative sales figures and growth figures from examples "Example: Compare sales figures across time using LAG/LEAD" and "Example: Calculate sales growth across time", and a RANK function.

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Calendar Quarter, Calendar Month, Sales SUM 

Sort Order 

Calendar Year, Calendar Quarter, Calendar Month 

Conditions 

Department = Video Sale OR Department = Video Rental 

Calculation Name 

Rank Growth 

Calculation 

RANK() OVER(PARTITION BY "Calendar Year" ORDER BY "Growth %" DESC ) 

Additional Calculations Required 

Previous Year = LAG(Sales SUM,1) OVER(PARTITION BY "Calendar Month" ORDER BY "Calendar Year" )

Growth % = ( Sales SUM-"Previous year" )*100/"Previous year" 

Figure 19-23 Worksheet containing the Rank Growth calculation


Text description of af_laglb.gif follows.
Text description of the illustration af_laglb.gif

The worksheet shows the league table position of sales growth. For example, the Rank Growth value for January 1999 is 3, which means that January was the third best performing month (i.e. the sales growth for the month of January between 1998 and 1999 was the third highest in the league table).

Notes

Statistical function examples

About statistics functions

Statistics functions are used to compute covariance, correlation, and linear regression statistics. Each function operates on an unordered set. They also can be used as windowing and reporting functions.

Examples:

"Example: Calculate linear regression"

Example: Calculate linear regression

This example computes an ordinary least-squares regression line that expresses the Profit SUM per month as a linear function of its Sales SUM. The following functions are used:

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Calendar Month, Sales SUM, Profit SUM 

Sort Order 

Calendar Year 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000 

Calculations 

Slope = REGR_SLOPE(Profit SUM,Sales SUM) OVER(PARTITION BY Calendar Year ORDER BY Profit SUM)

Intercept = REGR_INTERCEPT(Profit SUM,Sales SUM) OVER(PARTITION BY Calendar Year ORDER BY Profit SUM)

Coefficient = REGR_R2(Profit SUM,Sales SUM) OVER(PARTITION BY Calendar Year ORDER BY Profit SUM)

Count = REGR_COUNT(Profit SUM,Sales SUM) OVER(PARTITION BY Calendar Year ORDER BY Profit SUM)

Average = REGR_AVGX(Profit SUM,Sales SUM) OVER(PARTITION BY Calendar Year ORDER BY Profit SUM)

Average 2 = REGR_AVGY(Profit SUM,Sales SUM) OVER(PARTITION BY Calendar Year ORDER BY Profit SUM) 

Figure 19-24 Worksheet containing the statistical calculations


Text description of af_st1.gif follows.
Text description of the illustration af_st1.gif

The worksheet shows for each month the slope, intercept, coefficient, count, and average values.

Notes:

More about the Discoverer analytic function template

When you paste a new analytic function into a Calculation box, Discoverer provides the following generic template to help you define the function:

The expressions are used as follows.

For more information about Oracle expressions, see "Getting more information".

About analytic functions and sequencing

When you use analytic functions in conditions, the way that you combine them with non-analytic functions affects the Discoverer data returned by the query. The following sequencing rules apply (for more information, see "Examples of sequencing"):

Examples of sequencing

To illustrate how sequencing affects the Discoverer data returned by a query, consider the following two examples:

Example one

In the first scenario, we apply two single conditions: Region = `Central', and Rank <= 3 (where Rank is an analytic function).

Example two

In the second scenario, we apply a multiple condition: Region = `Central' AND Rank <= 3 (where Rank is an analytic function).

Oracle9i analytic function examples

The examples in this section show you how to use the Oracle9i analytic functions with Discoverer to perform detailed data analysis.

This section contains the following topics:

Notes

About getting more information

For more information about Oracle9i functions, refer to the following Oracle publications:

About inverse percentile examples

You use inverse percentile functions to work out what value computes to a certain percentile (i.e. the cumulative distribution of a set of values). For example, to calculate the median (i.e. middle value in a series) profit value.

Examples:

Inverse percentile functions can be used as window reporting functions and aggregate functions.

Two inverse percentile functions are available:

  1. PERCENTILE_CONT - a continuous function defined by interpolation (i.e. an estimate of a value of a function or series between two known values). Here, the function computes the percentile by linear interpolation between ordered rows.

  2. PERCENTILE_DISC is a step function that assumes discrete values. Here, the function scans the cumulative distribution value (using CUME_DIST) in each group to find the first value greater than or equal to the specified percentile value.

Note: Inverse percentile functions do the opposite of the CUME_DIST function, which works out the cumulative distribution of a set of values.

About differences between PERCENTILE_CONT and PERCENTILE_DISC

PERCENTILE_CONT and PERCENTILE_DISC might return different results, depending on the number of rows in the calculation. For example, if the percentile value is 0.5, PERCENTILE_CONT returns the average of the two middle values for groups with even number of elements. In contrast, PERCENTILE_DISC returns the value of the first one among the two middle values. For aggregate groups with an odd number of elements, both functions return the value of the middle element.

Example: Compute the median profit using the PERCENTILE_DISC function

This example computes the median profit value for cities using the PERCENTILE_DISC function as a reporting aggregate function.

Worksheet options  Set to: 

Items 

Video Analysis Information: City, Profit SUM 

Sort Order 

Not applicable 

Conditions 

Not applicable 

Calculation Name 

Median (PERCENTILE_DISC) 

Calculation 

PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY Profit SUM) OVER() 

Additional Calculations Required 

Cumulative Distribution = CUME_DIST() OVER(ORDER BY Profit SUM ) 

Figure 19-25 Worksheet containing the Median(PERCENTILE_DISC) calculation


Text description of af_per_d.gif follows.
Text description of the illustration af_per_d.gif

The worksheet shows the median profit value for cities. The median profit value (i.e. 0.50 in the Cumulative Distribution column) is $61,942,21 (i.e. the value for Pittsburgh, which has the value 0.50 in the Cumulative Distribution column).

Notes

Example: Compute the median profit using the PERCENTILE_CONT function

This example computes the median profit value for cities using the PERCENTILE_CONT function as a reporting aggregate function.

Worksheet options  Set to: 

Items 

Video Analysis Information: City, Profit SUM 

Sort Order 

Not applicable 

Conditions 

Not applicable 

Calculation Name 

Median (PERCENTILE_CONT) 

Calculation 

PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY Profit SUM) OVER() 

Figure 19-26 Worksheet containing the Median(PERCENTILE_CONT) calculation


Text description of af_per_c.gif follows.
Text description of the illustration af_per_c.gif

The worksheet shows the median profit value for cities. The median profit value is $63,076.41, which is the average profit value for the 0.50 and 0.55 percentile. In other words, the value for Pittsburgh plus the value for Denver, divided to two ($61,942.21 + $64,210.60)/2. For more information about how this function is calculated, see "About differences between PERCENTILE_CONT and PERCENTILE_DISC".

Notes

Hypothetical rank and distribution examples

You use hypothetical rank and distribution functions for 'what-if?' analysis. These functions work out the position of a value if the value was inserted into a set of other values. For example, where would a person who generated sales of $1,200,000 be positioned in a league table of sales peoples' performance.

Note: You can also calculate the hypothetical values of the following:

Examples:

Example: Calculate hypothetical rank

This example calculates the hypothetical rank of profit values in relation to profit values for departments and regions. For example, to answer the question, how would a sales value of $500.00 be positioned in a league table of values for the Video Sale department in each region?

Note: This example uses a parameter to provide dynamic input to the calculation (for more information see "About using parameters to provide dynamic input to calculations").

Worksheet options  Set to: 

Items 

Video Analysis Information: Calendar Year, Region, Department, Profit, Profit COUNT, Profit MAX, Profit MIN 

Sort Order 

Region (Lo to Hi) 

Conditions 

Not applicable 

Page Items 

Calendar Year=2000, Department=Video Sale, Value of Parameter Hypothetical Value=500 

Parameters 

Hypothetical Value - this value is entered by the Discoverer user when the worksheet is opened or refreshed. 

Calculation Name 

League table 

Calculation 

RANK(:Hypothetical Value) WITHIN GROUP(ORDER BY Profit DESC NULLS FIRST ) 

Figure 19-27 Worksheet containing the League table calculation


Text description of af_hypo_.gif follows.
Text description of the illustration af_hypo_.gif

Notes

Banding example

You use the Oracle9i WIDTH_BUCKET function to divide values into groups (sometimes called bands or buckets) according to their value (for more information, see "About banding"). For example, to group data for a bar graph.

Hint: You can also use the GREATEST and the CASE functions to calculate equi-width bands (see "Example: Producing equi-width bands (1)" and "Example: Producing equi-width bands (2)".

Examples:

Example: Producing equi-width bands using WIDTH_BUCKET

This example divides profit figures into three bands according to their value.

Worksheet options  Set to: 

Items 

Video Analysis Information: City, Profit SUM 

Sort Order 

Equi-width bands 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000

Region = Central 

Calculation Name 

Equi-width bands 

Calculation 

WIDTH_BUCKET(Profit SUM,0,30000,3) 

Figure 19-28 Worksheet containing the Equi-width bands calculation


Text description of af_band2.gif follows.
Text description of the illustration af_band2.gif

The worksheet shows equi-width bands for profit values for cities. The first band (0 to 9,999) contains Nashville, Minneapolis, Dallas, and Chicago. The second band (10,000 to 19,999) contains St. Louis. The third band (20,000 to 30,000) contains Cincinnati and Louisville.

Notes

The worksheet shows equi-width bands for profit values for cities. The first band (20,000 to 30,000) contains Cincinnati and Louisville. The second band (10,000 to 19,999) contains St. Louis. The third band (0 to 9,999) contains Chicago, Dallas, Minneapolis, and Nashville.

FIRST/LAST aggregate examples

You use FIRST/LAST aggregate functions to find the first or last value within an ordered group. This enables you to order data on one column but return another column. For example, to find the average sales transaction amount for the region with the largest number of sales transactions in a period.

Examples:

Using FIRST/LAST functions maximizes Discoverer performance by avoiding the need to perform self-joins or sub-queries.

Note: You can use FIRST/LAST functions with the following:

Example: Find the largest sales transaction in the area with most sales transactions

This example finds the largest sales transaction amount for the city with the most sales transactions in a period.

Worksheet options  Set to: 

Items 

Video Analysis Information: City, Sales MAX, Sales Count 

Sort Order 

Not applicable

 

Conditions 

Department = Video Sale OR Department = Video Rental

Calendar Year = 2000

Region = Central 

Calculation Name 

Maximum sales in city with largest sales volume 

Calculation 

MAX(Sales MAX) KEEP(DENSE_RANK LAST ORDER BY Sales COUNT ) OVER(PARTITION BY "Calendar Year", Region ) 

Figure 19-30 Worksheet containing the Maximum sales in city with largest sales volume calculation


Text description of af_firsa.gif follows.
Text description of the illustration af_firsa.gif

The worksheet shows the largest sales transaction value in the city with the largest number of sales transactions. Cincinnati has the largest number of sales transactions (1220). The largest sales transaction for Cincinnati is $667.53.

Notes

Example: Find the average sales transaction in the area with least sales transactions

This example calculates the average sales transaction amount for the city with the smallest number of sales transactions in a period.

Worksheet options  Set to: 

Items 

Video Analysis Information: City, Sales COUNT, Sales AVG 

Sort Order 

Not applicable

 

Conditions 

Department = Video Sale OR Department = Video Rental

Region = Central

Calendar Year = 2000 

Calculation Name 

Average sales in city with smallest sales volume 

Calculation 

MIN(Sales AVG) KEEP(DENSE_RANK FIRST ORDER BY Sales COUNT ) OVER(PARTITION BY "Calendar Year", Region ) 

Figure 19-31 Worksheet containing the Average sales in city with smallest sales volume calculation


Text description of af_f2.gif follows.
Text description of the illustration af_f2.gif

The worksheet shows the average sales transaction value in the city with the smallest number of sales transactions. Nashville has the smallest number of transactions in the period (219). Therefore, the calculation returns the average transaction value for Nashville ($38.39).

Notes


Go to previous page Go to next page
Oracle
Copyright © 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index