CALC(expression)
Calculations allow for additional flexibility in your reporting. They can be used to:
- Compare a value to its previous value
- Compare a value to an overall value
- Combine multiple properties into a new value
Calculations take a mathematical expression. They may be made up of properties (at the datapoint level), aggregates, and the result of PREV/NEXT functions.
To see sample reports using calculations, click here.
Caveats:
- Special care must be taken in situations that may result in divide by zero errors (see below for example)
- If you want to get decimal precision out of integers, you need to multiply by 1.0 (see below for example)
#datapoints with calculations{|RESULT|}
<table class='data'>
<thead>
<tr>
<th>ID</th>
<th>Date</th>
<th>Score</th>
<th>Compared to Score of Previous Shop</th>
<th>Bonus Scores</th>
</tr>
</thead>
<tbody>
{|LOOP|RESULT.DATAPOINTS(SORT=TRENDING_TIMESTAMP)}
<tr>
<th>{DATAPOINTS.DATAPOINT_ID}</th>
<td>{DATAPOINTS.TRENDING_TIMESTAMP}</td>
#the current datapoint's score
<td>{DATAPOINTS.SCORE}</td>
#the current datapoint's score compared to previous datapoint's score
<td>{CALC(DATAPOINTS.SCORE - PREV(DATAPOINTS.SCORE))}</td>
#the current datapoints "bonus score" values
<td>{CALC(DATAPOINTS.BONUS_SCORE_1 + DATAPOINTS.BONUS_SCORE_2)}</td>
</tr>
{/|LOOP|}
</tbody>
</table>
{/|RESULT|}
#crosstab with calculations
{|CROSSTAB|}
{|LOOP|CROSSTAB.LOCATION(SORT=ORDER_NUM)}
{|SHOWIF|LOOP.ISFIRST}
<table class='data'>
<thead>
<tr>
<th>Name</th>
#note that we're sorting our month-years backward
{|LOOP|LOCATION.MONTH_YEAR(SORT=ORDER_NUM|DESC)}
<th>{MONTH_YEAR.NAME} Score</th>
<th>Compared to {MONTH_YEAR.NAME} Score 6 Months Ago</th>
<th>Compared to average for year</th>
{/|LOOP|}
</tr>
</thead>
<tbody>
{/|SHOWIF|}
<tr>
<th>{LOCATION.NAME}</th>
{|LOOP|LOCATION.MONTH_YEAR}
#the current location's average score for this month/year
<td>{AVG(MONTH_YEAR.SCORE)}</td>
#the current location's average score for this month/year compared
#to the current location's average score 6 months ago
#note the use of NEXT because we're trending backward
<td>{CALC(AVG(MONTH_YEAR.SCORE) - NEXT(AVG(MONTH_YEAR.SCORE), 6))}</td>
#the current location's average score for this month/year compared to
#average for this location overall
<td>{CALC(AVG(MONTH_YEAR.SCORE) - AVG(MONTH_YEAR.SCORE,LOCATION))}</td>
{/|LOOP|}
</tr>
{|VANISHIF|LOOP.HASNEXT}
</tbody>
</table>
{/|VANISHIF|}
{/|LOOP|}
{/|CROSSTAB|}
#handling potential divide-by-zero errors
{CALC(CASE WHEN COUNT(MONTH_YEAR,OVERALL) > 0 THEN COUNT(MONTH_YEAR) * 100.00 / COUNT(MONTH_YEAR,OVERALL) ELSE NULL END)}
#ensuring decimal precision when dealing with integers
{CALC(DATAPOINTS.HOURS_WORKED * 1.0 / DATAPOINTS.HOURS_QUOTED).ROUND(2)}