topic Re: using columnno() inside sum() in App Development

using columnno() inside sum()

Anonymous — Sun, 13 Nov 2016 07:49:28 GMT

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

kaushiknsolanki — Sun, 13 Nov 2016 07:51:20 GMT

Hi Roland,

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

Regards,

Kaushik Solanki

Re: using columnno() inside sum()

Anonymous — Sun, 13 Nov 2016 09:21:42 GMT

having a load table with

week,

mat,

price,

calcprice1,

calcprice2,

calcprice3

calcprice104

in a pivot I have

Dimensions:

mat

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

its_anandrjs — Sun, 13 Nov 2016 10:43:38 GMT

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

Gysbert_Wassenaar — Sun, 13 Nov 2016 10:47:00 GMT

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

Anonymous — Sun, 13 Nov 2016 10:50:54 GMT

Hi,

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

Re: using columnno() inside sum()

Anonymous — Tue, 15 Nov 2016 10:22:16 GMT

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) & ')';

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

THX for the help!!!