How can you use SUM(Column(n)) ?

[#1]

Hey there,

I have a very complex set of calculations I am doing in a straight table. I made a simple example, as seen below, to explain my problem:

Column A is my dimension.

Calculated expressions:

Column D : column(1) * column(2)

Column G : column(4) * column(5)

Column H : column(3) + column(6)

Column I : SUM(column(7)) / SUM(total column(7))

With Column I the problem comes in, it does not calculate. When ticking the "Relative" box on the Expressions tab it gives the correct answer, but it does not allow you to use that percentage. Is there some way to solve this?

Thank you,

Elmarie

[sunny_talwar]

It does not allow to use percentage? Can you share a sample?

[settu_periasamy]

Hi,

it seems, sum function is not working with column() / label name of column. But Rangesum is working..

e.g

      sum(column(7)) -> gives 0  but rangesum(column(7)) gives values

so, you can try like

=[Total cost]/Rangesum(sum(TOTAL [2015 price]*[2015 Quantity]),
                        sum(TOTAL [2016 Price]*[2016 Quantity]))

Here, [Total cost] is a Label name and check the Relative option..

Suggestion : Instead of using column(1), column(2) , You can directly use the label name.

like [2015 price]*[2015 Quantity] (careful with your original field name)

Sample Attached

[PrashantSangle]

Hi,

Use actual expression instead of column No or column Name.

Regards,

[jonathandienst · Accepted solution]

The Column() function and references to the expression name operate on a strictly row-by-row basis. It is not possible to use multirow aggregations like Sum, but the inter-record functions Above() and Below() work. It is possible to use this  construct in place of Sum(Column(1)):

=Rangesum(Above(Column(1), 1, NoOfRows(TOTAL)))+RangeSum(Below(Column(1), 0, NoOfRows(TOTAL)))

(or with column names)

But it may be better to use the expression as max dreamer says, although you may need to wrap the expression in Sum(Aggr(...))

[#6]

Like I said my calculations are very complex in the qvd I am developing with a vast amount of variables. So using the column names is not the solution. I just do not understand why QlikView does not have this functionality to use sum and column.

On another note, thank you Jonathan! It works perfectly now.

Thank you everyone for your help, really appreciate it!

Kind regards,

Elmarie