topic Re: How can you use SUM(Column(n)) ? in QlikView

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

Tue, 05 Apr 2016 13:36:28 GMT

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

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

sunny_talwar — Tue, 05 Apr 2016 16:25:02 GMT

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

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

settu_periasamy — Wed, 06 Apr 2016 03:57:04 GMT

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

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

PrashantSangle — Wed, 06 Apr 2016 04:50:45 GMT

Hi,

Use actual expression instead of column No or column Name.

Regards,

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

jonathandienst — Wed, 06 Apr 2016 05:08:12 GMT

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(...))

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

Wed, 06 Apr 2016 07:36:37 GMT

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