using columnno() inside sum()

[Anonymous]

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

[kaushiknsolanki]

Hi Roland,

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

Regards,

Kaushik Solanki

[Anonymous]

having a load table with

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

[its_anandrjs]

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

Ex:-

$(vExpr1)

$(vExpr2)

Regards

Anand

[Gysbert_Wassenaar]

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‌

[Anonymous]

Hi,

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

[Anonymous]

Hi,

I got a solution, which is doing the work:

Measure Calculation:

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

Loadscript:

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!!!