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:
- Oracle8i SQL Reference
- Oracle8i Data Warehousing Guide
- Oracle9i SQL Reference
- Oracle9i Data Warehousing Guide
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
- Examples in this section use a selection of commonly used commands. For a complete list of commands and their full syntax, refer to the Oracle SQL and warehousing guides.
- For more information on how to create calculations, see "Using calculations".
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().
Figure 19-1 Worksheet containing the Rows returned calculation
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
- ROWCOUNT does not count NULL values. To calculate the number of rows returned by a query, including NULL values, do the following:
- first create a temporary item called 'One record', with the formula = '1'
- create a calculation called 'Rows returned' to count the occurrences of One record, with the formula = SUM(Video Sales Analysis.One record)
Example: Calculate a 25% increase in sales
This example calculates a 25% increase in sales.
Figure 19-2 Worksheet containing the 25% sales increase calculation.
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
Figure 19-3 Worksheet containing the City(Upper Case) calculation
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
- Examples in this section use a selection of commonly used commands. For a complete list of commands and their full syntax, refer to Oracle8i SQL Reference and Oracle8i Data Warehousing Guide.
- For more information on how to create calculations, see "Using calculations".
Analytic function categories
Analytic functions are classified in the following categories:
- Ranking - Address business questions like: `What are the top 10 and bottom 10 salespeople per region?'.
- Banding - Address business questions like `What brands make up 25% of sales?'.
- Windowing - Address business questions like `What is the 13-week moving average of a stock price?' or `What is the cumulative sum of sales per region?'.
- Reporting Aggregates - After a query has been processed, aggregate values like the number of resulting rows, or the sum of a column in a set of rows. Address questions like `What are each product's Sales as a percentage of Sales for its product group?'.
- Lag/Lead - Address business questions like `What was the value of sales for the same period one year ago?'.
- Statistics - Perform statistical analysis with Business Intelligence OLAP/spreadsheet applications. For example, covariance and linear regression functions.
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:
RANK() OVER(ORDER BY 'Sales')
This example ranks sales figures (defined in the `Sales' item).
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.
Figure 19-4 Worksheet containing the Rank Sales calculation
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
- By default, ranked results data is sorted in ascending order (ASC), nulls first (NULLS FIRST). The additional DESC parameter sorts the results in descending order, which ranks the highest value with the Rank 1.
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.
Figure 19-5 Worksheet containing the Rank sales within Region calculation
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
- By default, ranked results data is sorted in ascending order (ASC), nulls first (NULLS FIRST). The additional DESC parameter sorts the results in descending order, which ranks the highest value with the Rank 1.
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.
Figure 19-6 Worksheet containing the Rank Top calculation used in a condition
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
- By default, ranked results data is sorted in ascending order (ASC), nulls first (NULLS FIRST). The additional DESC parameter sorts the results in descending order, which ranks the highest value with the Rank 1.
- Hint: To quickly filter the list to the first, second, or third ranked cities, pivot the Rank Top item to the page axis.
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.
Figure 19-7 Worksheet containing the Rank Top calculation used in a condition
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
- This analysis involves three steps:
- Assign ranks to Cities on Sales SUM in descending order, as Rank Top.
- Assign ranks to Cities on Sales SUM in ascending order, as Rank Bottom.
- Displaying only Rank Top, filter the data using a Condition to return only the top three and bottom three ranked Brands.
- In the example, in the `Central' Region, the top three cities are ranked 1, 2, and 3; the bottom three cities are ranked 5, 6, and 7.
In the `East' Region, the top three cities are ranked 1, 2, and 3; the bottom three cities are ranked 6, 7, and 8, and so on.
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:
- Banding by value - this divides values into groups according to their value (also known as equi-width bands).
Here, the function typically takes the largest value minus the lowest value, and divides the result by the number of bands required. This value defines the range of each Band.
Values are then assigned to bands according to which range they fall into. Therefore, the number of values in each Band might differ.
For example, if we have 100 values and divide them into four equi-width bands, each band might contain different numbers of values.
Figure 19-8 Banding By value
Text description of the illustration af_bval.gif
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").
- Banding by rank - this divides values into groups according to their rank (also known as equi-height bands).
Here, the function divides the number of values in the partition by the number of bands, which gives the number of values in each band.
An equal number of values are then placed in each band.
For example, if we have 100 values and divide them into four equi-height bands, each band contains 25 values.
Figure 19-9 Banding By Rank
Text description of the illustration af_brank.gif
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)".
Figure 19-10 Worksheet containing the Sales Bands calculation
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
- Using the Central Region and Year 2000 as an example, this function takes the largest value (45,758) minus the smallest value (7,749) and divides it by four ((45,758-7,749)/4), giving four equal Bands of 9,502.25. This gives four bands with the following ranges:
- Band 1 - 36,255.75 to 45,758
- Band 2 - 26,753.5 to 36,255.75
- Band 3 - 17,251.25 to 26,753.5
- Band 4 - 7,749 to 17,251.25
- Each value is placed in one of the four Bands depending on which range the Sales SUM value falls into.
- The FLOOR function returns the largest integer equal to or less than n. For example, in Dallas, the expression FLOOR(Sales SUM-Min Sales for Region) returns the smallest integer value from 7,749 minus 7,749, which returns 0. When used in conjunction with the GREATEST function (see calculation above), the expression GREATEST(1,4-FLOOR((Sales SUM-Min Sales for Region) returns the largest value from either 1 or, 4 minus the smallest integer value from 7,749 minus 7,749 (4 minus 0 equals 4). In other words, the expression returns the value 4.
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)").
Figure 19-11 Worksheet containing the Sales Bands 2 calculation
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.
Figure 19-12 Worksheet containing the Sales Bands 3 calculation
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
- Using the Central Region and Year 2000 as an example, this function takes the number of values (which is six) and divides it by two, giving three values per Band. It then takes the list of values ordered by Sales SUM and places values one, two, and three in band 1, values four, five, and six in band 2.
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:
- Windowing with logical offsets - here, the offset is based on a value relative to an existing value. For example, three months preceding a date value.
For example, if we have a list of monthly sales figures, a logical window might compute a moving average of the previous three months (inclusive of the current month). When calculating the average, the calculation assumes a NULL value for months missing from the list. In the example below, the three-month moving average for November assumes NULL values for the missing months September and October.
Text description of the illustration af_win1.gif
- Windowing with physical offsets - here, the offset is based on a value that is a specified number of rows from an existing value. For example, three rows from the current item.
For example, if we have a list of monthly sales figures, a physical window might compute a moving average of the previous three rows. When calculating the average, the calculation ignores months missing from the list. In the example below, the three-month moving average for November uses June, July, and November in the calculation.
Text description of the illustration af_win2.gif
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.
Figure 19-13 Worksheet containing the Moving Average calculation
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
- Note that you define the RANGE INTERVAL as `2', not `3', even though you want a three month window. This is because the window expression implicitly includes the current row. Therefore, in this example, the INTERVAL `2' plus the current row gives a total of three months (2 + current row = 3).
Example: Show the cumulative values of sales
This example uses a physical window to calculate the cumulative value of sales.
Figure 19-14 Worksheet containing the Cumulative Total calculation
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.
Figure 19-15 Worksheet containing the Sales Last Year calculation
Text description of the illustration af_win3.gif
For each row, the worksheet shows the sales total for the previous year.
Notes:
- In the example above, the Sales Last Year value for 1998 is NULL because the database does not contain information for 1997.
- You can also use LAG/LEAD functions to compare values across time (see "LAG/LEAD function examples".
Reporting function examples
About reporting functions
Reporting functions are used to compute aggregates.
Examples:
Example: Calculate annual sales
This example calculates annual sales.
Figure 19-16 Worksheet containing the Annual Sales calculation
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.
.
Figure 19-17 Worksheet containing the Annual Sales by Region calculation
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.
Figure 19-18 Worksheet containing the % of Annual Sales calculation
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.
Figure 19-19 Worksheet containing the % of Annual Sales calculation
Text description of the illustration af_repob.gif
The worksheet shows the sales value for cities as a percentage of total sales.
Notes:
- The function RATIO_TO_REPORT computes the ratio of a value to the sum of a set of values.
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.
- LAG - provides access to more than one row of a table at the same time without a self-join.
- LEAD - provides access to a row at a given offset after the current position.
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.
Figure 19-20 Worksheet containing the Previous Year calculation partitioned by Calendar Month
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
- Because there are no comparative figures for 1998, the Previous Year values for 1998 are blank.
- Notice that the value '1' in the LAG(Sales SUM,1) clause calculates the value from one year previously. For example, if you changed this value to '2', you would calculate the value from two years previously.
- Notice that the calculation includes the clause 'PARTITION BY Calendar Month', which gives you a value for each combination of Calendar Year (in the ORDER BY clause) and Calendar Month (in the PARTITION BY clause). In other words, the Previous Year value for February 1999 is the Sales SUM value for February 1998. If you removed this clause, you would calculate the value for the previous month (see example below). In other words, the Previous Year value for February 1999 would be the Sales SUM value for January 1999.
Figure 19-21 Worksheet containing the Previous Year calculation with the partition removed
Text description of the illustration af_ll2.gif
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".
Figure 19-22 Worksheet containing the Growth calculation
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
- Because there are no comparative figures for 1998, the Growth values for 1998 are blank.
- The calculation subtracts the Previous Year value from the Sales SUM value, then multiplies the result by the Sales SUM value divided by the Previous Year value. For example, if sales have risen from 75 to 100, the calculation becomes 25 * 1.33, giving 33.33% increase.
- For more information about the calculation Previous Year, see "Example: Compare sales figures across time using LAG/LEAD".
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.
Figure 19-23 Worksheet containing the Rank Growth calculation
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:
- SLOPE - slope of determination of the regression line
- INTERCEPT - intercept of determination of the regression line
- REGR_R2 - coefficient of determination of the regression line
- REGR_COUNT - number of items
- REGR_AVGX - average sales
- REGR_AVGY - average profit
Figure 19-24 Worksheet containing the statistical calculations
Text description of the illustration af_st1.gif
The worksheet shows for each month the slope, intercept, coefficient, count, and average values.
Notes:
- For more information about regression analysis, refer to Oracle8i SQL Reference and Oracle8i Data Warehousing Guide.
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.
- OVER - indicates that the function operates on a query result set, after the other query clauses have been applied (e.g. FROM, WHERE, HAVING).
- PARTITION BY - partition (or group) the query results set (e.g. PARTITION BY `Region').
- ORDER BY - specify how the results set is logically ordered (e.g. ORDER BY `Sales SUM').
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
- Examples in this section use a selection of commonly used commands. For a complete list of commands and their full syntax, refer to the Oracle9i SQL Reference and the Oracle9i Data Warehousing Guide.
- For more information on how to create calculations, see "Using calculations".
About getting more information
For more information about Oracle9i functions, refer to the following Oracle publications:
- Oracle9i SQL Reference
- Oracle9i Data Warehousing Guide
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:
- 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.
- 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.
Figure 19-25 Worksheet containing the Median(PERCENTILE_DISC) calculation
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
- On table worksheets, the calculation displays the returned value for each row in the worksheet. To return a single value, move the calculation into the Page Items area.
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.
Figure 19-26 Worksheet containing the Median(PERCENTILE_CONT) calculation
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
- If the number of rows in the calculation is even, the two middle results are averaged. In the example above, the values for the .50 and .55 percentile are averaged.
- On table worksheets, the calculation displays the returned value for each row in the worksheet. To return a single value, move the calculation into the Page Items area.
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:
- DENSE_RANK - computes the rank of values where equal values receive the same rank (e.g. you can have more than one value ranked as top of the league)
- CUME _DIST - computes the relative position of a specified value in a group of values
- PERCENT_RANK - similar to CUME_DIST, this function calculates the rank of a value minus 1, divided by 1 less than the number of rows being evaluated
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").
Figure 19-27 Worksheet containing the League table calculation
Text description of the illustration af_hypo_.gif
- The worksheet shows where the hypothetical value of $500 would rank in a league table of regions:
Notes
- The items Profit COUNT, Profit MAX, Profit MIN are not used in the calculation. They are displayed on the worksheet to help illustrate how the function is working. For example, if you can see that the Profit MAX value is $484.01 in the West region, you can see why a hypothetical value of $500.00 ranks as 1. This is because the hypothetical value is greater than the maximum value (i.e. the item Profit MAX).
- The League table calculation uses the value of the :Hypothetical Value parameter, entered when the worksheet is opened or refreshed. In the example below, the Hypothetical Value (displayed in the Hypothetical amount page item) is set to 500. For more information about using parameter values in calculations, see "About using parameters to provide dynamic input to calculations".
- The Rank function must take a non-aggregated value as an ORDER BY argument. For example, you could not perform this function on SUM(Profit) or Profit AVG.
- As an alternative to setting the Hypothetical Value as a parameter, you could enter the rank value directly into the calculation as the Rank() argument. For example:
- If you do not use a parameter, you will have to change the calculation to change the hypothetical value.
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.
Figure 19-28 Worksheet containing the Equi-width bands calculation
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 WIDTH_BUCKET function takes four arguments:
- worksheet item = Profit SUM
- minimum value = 0
- maximum value = 30000
- number of bands = 3
- To assign bands in reverse order, reverse the minimum and maximum values. For example, WIDTH_BUCKET(Profit SUM,30000,0,3). This function produces the worksheet below.
Figure 19-29 Worksheet containing the Equi-width bands calculation with reversed order
Text description of the illustration af_band3.gif
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:
- MIN - find the smallest value in a list of values
- MAX - find the largest value in a list of values
- AVG - find the average value of a list of values
- STDDEV - find the standard deviation of a list of values
- VARIANCE - find the variance of a list of values
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.
Figure 19-30 Worksheet containing the Maximum sales in city with largest sales volume calculation
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
- Sales MAX - contains the largest sales transaction amount.
- Sales COUNT - contains the number of sales transactions in the period.
- To apply the function, Discoverer does the following:
- orders the Sales COUNT column in the database (default order is ascending)
- takes the last value in Sales COUNT column (i.e. the LAST argument), which is the largest number, and looks up the city name for this row (Cincinnati)
- orders transactions in the database for Cincinnati and returns the LAST value, $667.53 (the default order is ascending)
- The Sales COUNT and Sales MAX items are included to demonstrate that the calculation returns the correct result. The Sales COUNT and Sales MAX items are not used to calculate the result, which is calculated using aggregation in the database.
- Hint: On table worksheets, the calculation displays the returned value for each row in the worksheet. To return a single value, move the calculation into the Page Items area.
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.
Figure 19-31 Worksheet containing the Average sales in city with smallest sales volume calculation
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
- Sales COUNT - contains the number of sales transaction in the period.
- Sales AVG - contains the average sales transaction amount in the period.
- To apply the function, Discoverer does the following:
- orders the Sales COUNT column in the database (default order is ascending)
- takes the first value in Sales COUNT column (i.e. the FIRST argument), which is the smallest number, and looks up the city name for this row (Nashville)
- calculates the average sales value for Nashville, $38.39
- The Sales COUNT and Sales AVG items are included to demonstrate that the calculation returns the correct result. The Sales COUNT and Sales AVG items are not used to calculate the result, which is calculated using aggregation in the database.
- Hint: On table worksheets, the calculation displays the returned value for each row in the worksheet. To return a single value, move the calculation into the Page Items area.
