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

    using columnno() inside sum()

    Roland Walde

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

          Hi Roland,

           

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

           

          Regards,

          Kaushik Solanki

            • Re: using columnno() inside sum()
              Roland Walde

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