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.

see also

Aggregated Expressions

Last modified: Apr 29, 2019


Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment