POS(property[, index])
Sometimes you need to reference data at a specific position in your data set. This can be used to:
- Sort a crosstab report by a specific column
- Compare a value to a specific baseline value
POS is most often combined with aggregates, but may be used at the datapoint level as well.
To get the value of the last group member or datapoint in a list, use LAST instead of a numeric index.
{|CROSSTAB|}<div>
#sort the locations by the first (most recent) month's score
{|LOOP|CROSSTAB.LOCATIONS(SORT=POS(AVG(MONTH_OF_YEAR.PERCENT_SCORE),1)|DESC)}
{|SHOWIF|LOOP.ISFIRST}
<table class='data'>
<thead>
<tr>
<th>Name</th>
{|LOOP|LOCATIONS.MONTH_OF_YEAR(SORT=ORDER_NUM|DESC)}
<th>{MONTH_OF_YEAR.NAME}</th>
{/|LOOP|}
</tr>
</thead>
<tbody>
{/|SHOWIF|}
<tr>
<th>{LOCATIONS.NAME} </th>
{|LOOP|LOCATIONS.MONTH_OF_YEAR}
<td>{AVG(MONTH_OF_YEAR.PERCENT_SCORE).NUMBERFORMAT(2)}</td>
{/|LOOP|}
</tr>
{|VANISHIF|LOOP.HASNEXT}
</tbody>
</table>
{/|VANISHIF|}
{/|LOOP|}
</div>
{/|CROSSTAB|}
{|RESULT|}{|LOOP|RESULT.MONTH_OF_YEAR(SORT=ORDER_NUM)}
{|SHOWIF|LOOP.ISFIRST}
<table class='data'>
<thead>
<tr>
<th>Month</th>
<th>Score</th>
<th>Compared to January</th>
</tr>
</thead>
<tbody>
{/|SHOWIF|}
<tr>
<th>{MONTH_OF_YEAR.NAME}</th>
<td>{AVG(MONTH_OF_YEAR.PERCENT_SCORE).ROUND()}</td>
#compare the current month's score to January's (1st) score
<td>{CALC(AVG(MONTH_OF_YEAR.PERCENT_SCORE) - POS(AVG(MONTH_OF_YEAR.PERCENT_SCORE),1)).ROUND()}</td>
</tr>
{|VANISHIF|LOOP.HASNEXT}
</tbody>
</table>
{/|VANISHIF|}
{/|LOOP|}
{/|RESULT|}