Page Comparison

{anchor:top}
{toc: class=contents}
h2. Overview
{styleclass: Class=topLink}[top|#top]{styleclass}
Advanced Functions are used to transform results by applying post processing calculations to the initial query results. These functions are database independant as they are performed by Java code, rather than being part of the SQL query.

h3. Available Functions
Yellowfin comes with a set of pre-defined functions.  However, your administrator may add in additional functions specifically for your organisation or reporting needs.  Expand the following to see the lists of functions available:

h4.{expand:title=Statistical}
|*Decile*|Decile divides the rows returned into 10 equal parts, and assigns a value of 1 to 10, based upon its rank to the highest value. Deciles are used as a measure of dispersion.|
|*Deviation*|The number of deviations from the mean.|
|*Linear Regression*|A linear trendline is a best-fit straight line that is used with simple linear data sets. Your data is linear if the pattern in its data points resembles a line. A linear trendline usually shows that something is increasing or decreasing at a steady rate.|
|*Mean*|The arithmetic mean (or simply the mean) of a list of numbers is the sum of all the members of the list divided by the number of items in the list.|
|*Median*|The median is described as the number separating the higher half of a sample, a population, or a probability distribution, from the lower half.|
|*Mode*|The mode is the value that occurs the most frequently in a data set|
|*Moving Average*|A moving average trendline smoothes out fluctuations in data to show a pattern or trend more clearly. 
A moving average uses a specific number of data points (set by the Period option), averages them, and uses the average value as a point in the line.
If Period is set to 2, for example, then the average of the first two data points is used as the first point in the moving average trendline. The average of the second and third data points is used as the second point in the trendline, and so on.|
|*Moving Total*|The total over the last N periods.|
|*Naïve Forecasting*|A naive forecasting model is a special case of the moving average forecasting model where the number of periods used for smoothing is 1. Therefore, the forecast for a period, t, is simply the observed value for the previous period, t-1.
Due to the simplistic nature of the naive forecasting model, it can only be used to forecast up to one period in the future. It is not at all useful as a medium-long range forecasting tool.|
|*Polynomial Regression*|A polynomial trendline is a curved line that is used when data fluctuates. It is useful, for example, for analysing gains and losses over a large data set. The order of the polynomial can be determined by the number of fluctuations in the data or by how many bends (hills and valleys) appear in the curve. An Order 2 polynomial trendline generally has only one hill or valley. Order 3 generally has one or two hills or valleys. Order 4 generally has up to three.|
|*Quartile*|Quartile divides the rows returned into 4 equal parts, and assigns a value of 1 to 4, based upon its rank to the highest value. Quartiles are used as a measure of dispersion.|
|*Standard Deviation*|The standard deviation is a measure of the dispersion of a set of values. It can apply to a probability distribution, a random variable, a population or a multiset.|
|*Standard Score*|The standard score indicates how many standard deviations an observation is above or below the mean. It allows comparison of observations from different normal distributions, which is done frequently in research.|
|*Variance*|Returns the difference between the data sets.|
|*Weighted Moving Average*|Returns a moving average that is weighted so that the more recent the value, the more weight is applied to it.|
{expand}

h4. {expand:title=Date}
|*Days Between Date*|The days between the date selected and another date column on the report.|
|*Days to Now*|The days between the date selected and the current date. (age in days)|
|*Months Between Date*|The months between the date selected and another date column on the report.|
|*Months to Now*|The months between the date selected and the current date. (age in months)|
|*Weeks Days Between*|The week days between the date selected and another date column on the report.|
|*Years Between Date*|The years between the date selected and another date column on the report.|
|*Years to Now*|The years between the date selected and the current date. (age in years)|
{expand}

h4. {expand:title=Text}
|*Concatenate*|Joins two columns into one text string.|  
{expand}

h4. {expand:title=Analysis}
|*Accumulative Percentage*|Will print a running percentage for the values returned.  A maximum of 100% will be displayed.|
|*Accumulative Total*|Will print out a running total for the data returned.|
|*Ascending Rank*|The highest value returned will be displayed as a 1.  Used where the preferable result is a higher value.  Eg. Profit.|
|*Bottom 10 Rank*|The bottom 10 values (lowest) are returned.|
|*Bottom N Rank*|The bottom N values (lowest) are returned- user is prompted to define number to return.|
|*Delta from Last*|Calculate change between consecutive rows|
|*Delta from Last N*|Calculate change between the current row and row - N|
|*Descending Rank*|The lowest value returned will be displayed as a 1.  Used where the preferable result is a lower value.  Eg. Expenses.|
|*Difference of Columns*|Returns difference of two selected columns|
|*Multiplication of Columns*|Returns multiplication of two selected columns|
|*Natural Logarithm*|Gives the base e logarithm of the values of a given field.|
|*Natural Logarithm*|Gives the base e logarithm of the values in the field.|
|*Percentage Against Absolute Maximum*|Returns percentage of selected field according to an absolute maximum value.|
|*Percentage Against Column*|Creates a percentage ration of values in the selected column compared to another column.|
|*Percentage Against Maximum*|Returns the % of the attribute when compared to the maximum value of the attribute within the dataset.|
|*Percentage of Total*|Returns the % of the attribute when compared to the total summed value of the attribute for the entire dataset.|
|*Sum of Columns*|Returns the sum of two selected columns.|
|*Top 10 Rank*|The top 10 values (highest) are returned.|
|*Top N Rank*|The top N values (highest) are returned - user is prompted to define number to return.|
|*Top N With Ties*|Returns top values for the selected field with provision for tied values. This means that if there are multiple records per ranking it will restrict it to N total rankings. |
{expand}
 
h2. Applying a Function
{styleclass: Class=topLink}[top|#top]{styleclass}

h3. Report Builder - Data pageStep
To apply and aadvanced function tofirst ayou fieldwill onneed theto Reportensure Datathe pagefield (Stepyou 1wish ofto theapply Reportit Builder)to #has Dragbeen theadded field ontoto your reporttable. #Often, Highlightwhen theapplying fieldfunctions, andyou clickmay find theyou functionneed icon.two copies of #the Thefield functionin popupyour willtable; open.one to Thisdisplay willthe displayoriginal avalues range(such ofas functionssales availablefigures) forand the particularsecond functionto type.have a Forfunction exampleapplied ifto youit have(for chosenexample, adisplaying datethe variabletop then10 onlyrank dateof functionssales willfigures).
be
displayed.To #apply Select thea function typeto anda thenfield thein specificyour function.table Byfrom highlightingthe theData functionsstep availableof the descriptionreport willbuilder, appearcomplete atthe thefollowing:
bottom# ofOpen the field's functiondrop list.
!01advFn.png|thumbnail,border=1!
# If appropriate select the aggregation.  For example you can create a running total for SUM of revenue rather than revenue.  This will pre-aggregate your data prior to the running total being applied.
# Click *Save* to close and save your function.
!02applied.png!
# When you run your report your column will now have the function formula applied.
!output1.png!

h3. Report Preview page
To apply a function to a field on the Report Preview/Format page
# Click on the drop down arrow on the column title of the field you want to apply the function to
# Select *Add f( x )* from the list
# The function popup will open.  This will display a range of functions available for the particular function type.  For example if you have chosen a date variable then only date functions will be displayed.
# Select the function type and then the specific function. By highlighting the functions available the description will appear at the bottom of the function list.
!01advFn.png|thumbnail,border=1!
# If appropriate select the aggregation.  For example you can create a running total for SUM of revenue rather than revenue.  This will pre-aggregate your data prior to the running total being applied.
# Click *Save* to close and save your function.
# Your report will now run, with an extra field, a copy of the original selected field with the function applied.
!output2.png!

{color:#CC0000}*Note:*{color} if you apply a function outside of the Report Data page, Yellowfin will create a duplicate of the selected field, ensuring you have the original value, and then a copy with the function applied.

h3. Chart Builder
To apply a function to a field on the Chart Builder page
# Generate your chart
# Select *+ Function* below your list of series
!addFnChart.png|thumbnail,border=1!
# Select the metric you wish to apply the function to
!selectMetric.png|thumbnail,border=1!
# The function popup will open.  This will display a range of functions available for the particular function type.  For example if you have chosen a date variable then only date functions will be displayed.
# Select the function type and then the specific function. By highlighting the functions available the description will appear at the bottom of the function list.
!01advFn.png|thumbnail,border=1!
# If appropriate select the aggregation.  For example you can create a running total for SUM of revenue rather than revenue.  This will pre-aggregate your data prior to the running total being applied.
# Click *Save* to close and save your function.
# Your report will now run, with an extra field, a copy of the original selected field with the function applied.
!output3.png|thumbnail,border=1!
{color:#CC0000}*Note:*{color} if you apply a function outside of the Report Data page, Yellowfin will create a duplicate of the selected field, ensuring you have the original value, and then a copy with the function applied.


h2. Removing a Function
{styleclass: Class=topLink}[top|#top]{styleclass}
To remove a function from an item, do one of the following:
# Reset the field by selecting the metric and clicking the clear button.
!04clear.png!
# Remove the field from the report entirely

 

h2. Statistical Functions
{styleclass: Class=topLink}[top|#top]{styleclass}
Yellowfin has a set of statistical functions which you can apply to your data.  These include regressions and moving averages.  These are applied as analytical functions and can result in trend lines such as the example below.

!05example.png|thumbnail,border=1!down menu, in either of these locations:
## Through the Columns/Rows list
## Through the Table Preview
# Now select the *Advanced Function* option from the list, opening the Advanced Function display.
# You will now need to complete the function options:
## Apply the appropriate aggregation to the field. This ensures the function is applied on top of any aggregations necessary.
## Select which function type you wish to use, from *Analysis*, *Statistical*, and *Text* 
## Select the name of the function you wish to use from the list. Once selected, you will see a description of the function displayed next to it. Some functions require extra parameters, which will need to be defined once the function has been selected.
## You also have the option to set the function to only display on the Charts page. This allows you to create additional copies of a field to apply functions to for graphical purposes, without cluttering your table with extra fields.
# When defined, click *Save* to apply the function.

h3. Report Builder - Charts Step
To apply a function to a field in your table from the Charts step of the report builder, complete the following:
# Click on the + button at the bottom left of the page
# Select *Advanced Function for* and choose the field you wish to use from the list, opening the Advanced Function display.
# You will now need to complete the function options:
## Apply the appropriate aggregation to the field. This ensures the function is applied on top of any aggregations necessary.
## Select which function type you wish to use, from *Analysis*, *Statistical*, and *Text* 
## Select the name of the function you wish to use from the list. Once selected, you will see a description of the function displayed next to it. Some functions require extra parameters, which will need to be defined once the function has been selected.
## You also have the option to set the function to only display on the Charts page. This allows you to create additional copies of a field to apply functions to for graphical purposes, without cluttering your table with extra fields.
# When defined, click *Save* to apply the function.

h3. Report Builder - Output Step
To apply a function to a field in your table from the Output step of the report builder, complete the following:
# Open the field's drop down menu
# Now select the *Advanced Function* option from the list, opening the Advanced Function display.
# You will now need to complete the function options:
## Apply the appropriate aggregation to the field. This ensures the function is applied on top of any aggregations necessary.
## Select which function type you wish to use, from *Analysis*, *Statistical*, and *Text* 
## Select the name of the function you wish to use from the list. Once selected, you will see a description of the function displayed next to it. Some functions require extra parameters, which will need to be defined once the function has been selected.
## You also have the option to set the function to only display on the Charts page. This allows you to create additional copies of a field to apply functions to for graphical purposes, without cluttering your table with extra fields.
# When defined, click *Save* to apply the function.

\\
\\
{horizontalrule}
{styleclass: Class=topLink}[top|#top]{styleclass}