topic Re: Adding calculated dimension for straight table in QlikView

Adding calculated dimension for straight table

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Adding calculated dimension for straight table

Sun, 28 Aug 2016 17:41:31 GMT

I have already solved. My problem was the case sensitive....

instead of:

=Aggr(Sum([Duration]), order)

is:

=Aggr(Sum([Duration]), Order)

Re: Adding calculated dimension for straight table

sunny_talwar — Sun, 28 Aug 2016 17:43:23 GMT

Why not just add Order as dimension and Sum(Duration) as your expression

Re: Adding calculated dimension for straight table

sunny_talwar — Sun, 28 Aug 2016 17:44:41 GMT

I still don't understand what you trying to do? Why not just Sum(Duration) as your expression? Why you need Aggr() function?

Re: Adding calculated dimension for straight table

Sun, 28 Aug 2016 20:29:30 GMT

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!