6 Replies Latest reply: Nov 15, 2016 5:22 AM by Roland Walde

# using columnno() inside sum()

Hi,

I have column with enumerating names: c1, c2, c3...

In the sum aggregation I would like to address these columns with a function like this:

sum(\$(='[c' & ColumnNo() &']')

But I cannot find out the correct synthax.

Any ideas?

Roland

• ###### Re: using columnno() inside sum()

Hi Roland,

Can you explain what you are trying to achieve? With some example.

Regards,

Kaushik Solanki

• ###### Re: using columnno() inside sum()

week,

mat,

price,

calcprice1,

calcprice2,

calcprice3

.

.

.

calcprice104

in a pivot I have

Dimensions:

mat

<MEASURES>

Columns:

week

MEASURES:

Measure1: sum(price)

Measure2: if (sum(\$(='[calcprice' & ColumnNo() &']')) <= 0, 7 * ColumnNo() + something, 999)

this is working:

if (sum(calcprice1) <= 0, 7 * ColumnNo(),

if (sum(calcprice2) <= 0, 7 * ColumnNo(),

if (sum(calcprice3) <= 0, 7 * ColumnNo(), 999)))

But, because I have to do it 104 times it would be a big if statement and I found out,

that there is a limitation of 96 nested IFs...

I also tried  sum(\$(='[calcprice' & \$(=ColumnNo()) &']'))

and: sum(\$(='calcprice' & \$(=ColumnNo()))

• ###### Re: using columnno() inside sum()

Try with nested expression with the variables also this will decrease your expression word length.

Ex:-

\$(vExpr1)

\$(vExpr2)

Regards

Anand

• ###### Re: using columnno() inside sum()

Perhaps something like this:

=pick(match(-1,\$(=concat('sum([' & 'calcprice' & valueloop(1,104) & ']) <=0',', ', valueloop(1,104))),-1),\$(=concat( '7*ColumnNo()',',', valueloop(1,104))),999)

sillytricksdepartment

• ###### Re: using columnno() inside sum()

Hi,

great, will check that tomorrow... did not know, that something like valueloop exists...

• ###### Re: using columnno() inside sum()

Hi,

I got a solution, which is doing the work:

Measure Calculation:

if( \$(vFinalCalc) > 0, \$(vFinalCalc), 7 * (104 - ColumnNo()))

for i = 1 To 104;

let i2 = i + 1;

let l\$(i) = 'sum(SOH) - rangesum(after(Sum(TD),1,\$(i2))) <= 0';

let r\$(i) = 'round((sum(SOH) - rangesum(after(Sum(TD),1,\$(i)))) / ((sum(SOH) - rangesum(after(Sum(TD),1,\$(i))))-(sum(SOH) - rangesum(after(Sum(TD),1,\$(i2)))))*7)';

let vOneRowCalcLeft = vOneRowCalcLeft & ',' & chr(36) & '(l' & \$(i) & ')';

let vOneRowCalcRight = vOneRowCalcRight & ',' & chr(36) & '(r' & \$(i) & ')';

Next

let vFinalCalc = 'match(-1 \$(vOneRowCalcLeft)) * 7 + pick(match(-1 \$(vOneRowCalcLeft)) \$(vOneRowCalcRight))';

THX for the help!!!