Span
The Span() function arranges multiple blocks of data into a single block of data and is often used to create reports consisting of multiple parts. Sets of data can be placed next to each other or below each other. If the source data changes, leading to increased output data, the combined output blocks are resized and moved accordingly so as not to overwrite one another.
Syntax
Span (Expression, Range 1, Range 2, … Range 18)
| Expression |
This expression determines how the input ranges will be arranged relative to one another. See below for more detail. |
| Range 1 | The first block of input data |
| Range 2 | The second block of input data |
| … | Subsequent blocks of input data |
| Range 18 | The function can accept a maximum of 18 inputs. If more are required, additional Span() function calls can be nested within the Span() function. |
Expression
* places two ranges next to each other
/ stacks a range on top of another range
The ranges are specified by “arr” appended with a number corresponding with the range input order. For example:
“arr1/arr2” will place the second range under the first one
“arr2*arr1” will place the second range next to and before the first one.
Brackets () determine the order in which to apply arrangements, for example, “(arr1*arr2)/(arr3*arr4)” will place arr1 and arr2 alongside each other, and will place arr3 and arr4 underneath arr1 and arr2, also alongside each other.
To add an empty row or column between the arrays, use a single empty string as an array ” “ – see Example 2. Arranging data
Note:
Input parameters can consist of Quintessence functions. This includes functions that return ranges in cases where a parameter value can be a range.
The Span() function allows functions to expand dynamically. Adjacent functions are moved accordingly so that they are not overwritten. Consider three functions f1, f2 and f3:
If the output from f1 expands it will overwrite functions f2 and f3 along with their output:
The span function is useful in this case, as it ensures that f2 and f3 are not overwritten, regardless of changes in the size of f1’s output. If f1’s output expands, f2 and f3 are moved accordingly:
Examples
Example 1. Stacking two input ranges vertically
Example 2. Arranging data using both ‘/’ and ‘*’
Example 3. Using Span to manage expanding output when arranging data
Note:
How your system administrator configured Quintessence determines which function parameters are required. In most implementations, administrators configure functions so that certain parameters default to specific values when left blank.
Example 1. Stacking two input ranges vertically
The Span() function is used to rearrange two input ranges (Countries A and Countries B) into a single column. The Span() function places arr2 (the second input range) beneath arr1 (the first input range) as specified by “/” in the expression.
| Countries A | Countries B | Rearrange Countries B under Countries A |
| Angola | Botswana | =Span(“arr1/arr2”,A2:A4,B2:B3) |
| Botswana | Cameroon | |
| Gambia |
Span() is used to stack the ranges on top of one another.
| Countries A | Countries B | Output |
| Angola | Botswana | Angola |
| Botswana | Cameroon | Botswana |
| Gambia | Gambia | |
| Botswana | ||
| Cameroon |
Example 2. Arranging data using both ‘/’ and ‘*’
In this example, both “/” and “*” are used to rearrange the input data. An empty array ” ” is used to insert a blank row between the top and bottom set.
| Asset Code | Asset Description | Asset Class | Sector | Subsector |
| YKG | YKG 0001 | Local Equity | Basic Materials | Steel |
| NHP | NHP 0001 | Local Equity | Consumer Goods | Food Products |
| QGW | QGW 0001 | Local Equity | Consumer Goods | Furnishing |
| JWQ | JWQ 0001 | Local Equity | Financials | Banks |
| GZW | GZW 0001 | Local Equity | Financials | Life Assurance |
| Span is used to rearrange the input data. |
| =Span(“(arr1*arr2)/arr3/(arr4*arr5)”,A1:A6,E1:E6,” “,E1:E6,D1:D6) |
| Output | |
| Asset Code | Subsector |
| YKG | Steel |
| NHP | Food Products |
| QGW | Furnishing |
| JWQ | Banks |
| GZW | Life Assurance |
| Subsector | Sector |
| Steel | Basic Materials |
| Food Products | Consumer Goods |
| Furnishing | Consumer Goods |
| Banks | Financials |
| Life Assurance | Financials |
Example 3. Using Span to manage expanding output when arranging data
In this example the TimeSeries() function retrieves two sets of data. The output requirement is that the two sets of data should be stacked on top of each other. Because of the dynamic nature of the Timeseries() function, the number of records returned could grow, resulting in the top set of data overwriting the bottom set. Using the Span() function allows the two TimeSeries functions to expand and move so as not to overwrite each other.
| TimeSeries() returns YKG close prices on 5 January 2015, starting from 3 January 2015 |
| =TimeSeries(“YKG”,”Close Price”,”3-jan-2015″,DateOffset(),,,”Both”) |
| Output | |
| YKG | Close Price |
| 2015/01/03 | 100 |
| 2015/01/04 | 101 |
| 2015/01/05 | 99 |
| TimeSeries() returns NHP close prices on 5 January 2015, starting from 3 January 2015 |
| =TimeSeries(“NHP”,”Close Price”,”3-jan-2015″,DateOffset(),,,”Both”) |
| Output | |
| NHP | Close Price |
| 2015/01/03 | 200 |
| 2015/01/04 | 195 |
| 2015/01/05 | 199 |
| Span() is used to stack the two sets of results on 5 January 2015 |
| =Span(“arr1/arr2”,TimeSeries(“YKG”,”Close Price”,”3-jan-2015″,DateOffset(),,,”Both”),TimeSeries(“NHP”,”Close Price”,”3-Jan-2015″,DateOffset(),,,”Both”)) |
| Output | |
| YKG | Close Price |
| 2015/01/03 | 100 |
| 2015/01/04 | 101 |
| 2015/01/05 | 99 |
| NHP | Close Price |
| 2015/01/03 | 200 |
| 2015/01/04 | 195 |
| 2015/01/05 | 199 |
| The span function is refreshed on 7 January 2015 |
| =Span(“arr1/arr2”,TimeSeries(“YKG”,”Close Price”,”3-Jan-2015″,DateOffset(),,,”Both”),TimeSeries(“NHP”,”Close Price”,”3-jan-2015″,DateOffset(),,,”Both”)) |
This results in a greater number of records returned by both TimeSeries functions (DateOffset without parameters = today’s date). The two functions and their results are resized and moved accordingly, so that the first set does not overwrite the next set.
| Output | |
| YKG | Close Price |
| 2015/01/03 | 100 |
| 2015/01/04 | 101 |
| 2015/01/05 | 99 |
| 2015/01/06 | 99.5 |
| 2015/01/07 | 101 |
| NHP | Close Price |
| 2015/01/03 | 199 |
| 2015/01/04 | 195 |
| 2015/01/05 | 199 |
| 2015/01/06 | 198 |
| 2015/01/07 | 199 |