Initially I load several excels with the same columns but different data:
LOAD
Order,
Operation
FROM
(biff, ooxml, embedded labels, table is Sheet1)
After the load I have a unique table containing all data from the excels.
Order | Operation | Duration (hours)
100 Op100-1 2
200 Op200-1 1
300 Op300-1 0,5
100 Op100-2 1
100 Op100-3 4
200 Op200-2 3
and so on...
One order can have one or more than one operation associated and each operation has a duration (in hours).
So now I have a straight table. This table has only one column, Order, but I would like to add a new column to it, a calculated dimension. So I my straight table, for the column I am adding I set the expression:
=Aggr(Sum([Duration]), order)
What I am trying to do is to obtain the sum of the durations for each order, for example, I want to present below data in my straight table:
Order | Duration
100 7
200 4
300 0,5
Order 100 has 3 operations Op100-1, Op100-2 and Op100-3 which durations are 2, 1 and 4 respectively, so the sum is 7. And so on for the rest of orders.
The problem is that it seems expression is not getting calculate well, all values are shown as '-' in the straight table for column Duration.
What's wrong?
Another possibility would be to calculated this column when loading it in the script but I would like to use a calculated dimension in my straight table for now.
thanks again!