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.
Example on adapting your data
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.
Post your comment on this topic.