Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Roland,
Can you explain what you are trying to achieve? With some example.
Regards,
Kaushik Solanki
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()))
Try with nested expression with the variables also this will decrease your expression word length.
Ex:-
$(vExpr1)
$(vExpr2)
Regards
Anand
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
Hi,
great, will check that tomorrow... did not know, that something like valueloop exists...
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!!!