Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Why not just add Order as dimension and Sum(Duration) as your expression
I have already solved. My problem was the case sensitive....
instead of:
=Aggr(Sum([Duration]), order)
is:
=Aggr(Sum([Duration]), Order)
Why not just add Order as dimension and Sum(Duration) as your expression
I still don't understand what you trying to do? Why not just Sum(Duration) as your expression? Why you need Aggr() function?
Hi, Sunny again,
What you proposed is working perfectly.
My solution also works, but yours is simpler, easier and readable. So point for you! thanks again!