## Introduction

An Expression may seem to perform the correct calculations, but it is important to double-check results during the development process, and question the calculations themselves.

One crucial point to remember is that fields must be aggregated in order to be displayed inside a widget. This performs a second layer of calculation and needs to be thought out carefully.

## The easy case : SUM

Sum is easy. Sum of sums gives the expected total.

If you configure an Expression like :

`ExpressionSUM = [DurationLoading] + [DurationPacking]`

You can easily use it in a widget with default aggregation SUM, and you should get the total durations as expected result.

The only things to look out for is :

• unit consistence (adding minutes with minutes)
• NULL handling (what happens when [DurationPacking] is NULL)

## The general case

Consider the following dataset :

Day GoodProduction TotalProduction ratio
1 8 80 0.1
2 2 2 1
3 3 15 0.2
4 3 3 1
(total) 16 100 should be 16/100 = 0.16

### ratio : First Attempt (wrong)

I create :

`Expression1 = [GoodProduction] / [TotalProduction]`

It seems to give good results in the Data Preview grid in Alpana Designer. But in the Data Preview, rows are not aggregated !

When I add the expression to a widget, what aggregation do I choose ?

`SUM([Expression])` will give 2.3 which is incorrect ! The reason is that you calculate the sum of ratios (0.1 + 1 + 0.2 + 1) which has no meaning.

`AVG([Expression])` will give 0.575 which is incorrect too ! The reason is that you calculate the un-weighted average of ratios (0.1 + 1 + 0.2 + 1)/4 which has no meaning also.

### ratio : Solution

The Expression must express what you really want to calculate, including aggregation :

`Expression2 = SUM([GoodProduction]) / SUM([TotalProduction])`

This will be used as a calculated column in all queries, including widgets and will always make the correct calculation.

For example, in the Total of the data above, you will correctly get : (8+2+3+3) / (80+2+15+3) = 0.16

There is only one fix needed :

Since we are making a ratio of integers, we should force to cast as float to make float division :

`Expression3 = (SUM([GoodProduction]) * 1.0) / SUM([TotalProduction])`

## General case : Averages and other

In many cases, this phenomenon can be hidden.

If your database field is called [AverageDuration], it is probably wrong to make a SUM or AVG of it, for the same reasons as above.

In some cases, you will need to expose to the dashboard Data Source the fields necessary to re-aggregate the calculation properly.

Your Data Source points to an SQL View with a calculated column “OEE”.

You made the calculation before reaching the dashboard, and you defined it as : `[OEE] = SUM([Uptime]) / (SUM([Uptime]) + SUM([Downtime]))`

Now in your Dashboard, how will you aggregate this OEE ? You will run into the issues described above as you will implicitly make wrong calculations.