Column() returns the value found in the column corresponding to ColumnNo in a straight table, disregarding dimensions. For example Column(2) returns the value of the second measure column.
Syntax:
Column(ColumnNo)
Return data type: dual
Arguments:
| Argument | Description |
|---|---|
| ColumnNo | Column number of a column in the table containing a measure. Information noteThe Column() function disregards dimension columns. |
Limitations:
-
Recursive calls will return NULL.
-
If ColumnNo references a column for which there is no measure, a NULL value is returned.
-
Sorting on y-values in charts or sorting by expression columns in tables is not allowed when this chart function is used in any of the chart's expressions. These sort alternatives are therefore automatically disabled. When you use this chart function in a visualization or table, the sorting of the visualization will revert back to the sorted input to this function.
Examples and results:
Example: Percentage total sales
| Customer | Product | UnitPrice | UnitSales | Order Value | Total Sales Value | % Sales |
|---|---|---|---|---|---|---|
| A | AA | 15 | 10 | 150 | 505 | 29.70 |
| A | AA | 16 | 4 | 64 | 505 | 12.67 |
| A | BB | 9 | 9 | 81 | 505 | 16.04 |
| B | BB | 10 | 5 | 50 | 505 | 9.90 |
| B | CC | 20 | 2 | 40 | 505 | 7.92 |
| B | DD | 25 | - | 0 | 505 | 0.00 |
| C | AA | 15 | 8 | 120 | 505 | 23.76 |
| C | CC | 19 | - | 0 | 505 | 0.00 |
Example: Percentage of sales for selected customer
| Customer | Product | UnitPrice | UnitSales | Order Value | Total Sales Value | % Sales |
|---|---|---|---|---|---|---|
| A | AA | 15 | 10 | 150 | 295 | 50.85 |
| A | AA | 16 | 4 | 64 | 295 | 21.69 |
| A | BB | 9 | 9 | 81 | 295 | 27.46 |
| Examples | Results |
|---|---|
|
Order Value is added to the table as a measure with the expression: Sum(UnitPrice*UnitSales). Total Sales Value is added as a measure with the expression: Sum(TOTAL UnitPrice*UnitSales) % Sales is added as a measure with the expression 100*Column(1)/Column(2) |
The result of Column(1) is taken from the column Order Value, because this is the first measure column. The result of Column(2) is taken from Total Sales Value, because this is the second measure column. See the results in the column % Sales in the example Percentage total sales. |
|
Make the selection Customer A. |
The selection changes the Total Sales Value, and therefore the %Sales. See the example Percentage of sales for selected customer. |
Data used in examples:
ProductData:
LOAD * inline [
Customer|Product|UnitSales|UnitPrice
Astrida|AA|4|16
Astrida|AA|10|15
Astrida|BB|9|9
Betacab|BB|5|10
Betacab|CC|2|20
Betacab|DD||25
Canutility|AA|8|15
Canutility|CC||19
] (delimiter is '|');