10 Replies Latest reply: Nov 13, 2017 4:15 PM by Christian Reed

# Get Column Number

Hi,

I have a bar chart with 4 expressions and no dimensions. It shows a bar for each one.

My expressions are: Reg1, Reg2, Reg3, Reg 4.

I know that i can reference these values in another expression by the function Column(). Like when i want a total in the chart i can use:

-column(1)+....+column(4)

or

-Reg1+..+.Reg4 (using the names).

How can i get the number of the columns? How can i be sure that Reg1 is column(1)?

Is there a formula that calculates the number of column Reg1 (which is also the first expression)? I am thinking about ColumnNo(Reg1) but it doesn't work.

• ###### Re: Get Column Number

You can use the labels of the expressions in your totaling expression. So Reg1+Reg2...+Reg4 will work if you set the labels of the expressions accordingly.

Column(1) is the first expression, Column(2) the second and so on. This is the order (top to bottom) of the expressions on the Expressions tab. The Promote and Demote button are used to change the order.

• ###### Re: Get Column Number

But is there a function that can calculate the number of Reg1 column? I am expecting that columnno(Reg1) shoul be 1 but it's not working. Any ideas?

• ###### Re: Get Column Number

I also had similar problem, since my expression label is dyanmic, i can't really do reg1+reg2 cuz reg1 can be xyz under certain conditions.

i have 70+ expressions in my table, counting them manually is just painful, i want to find out whats my current column num.

• ###### Re: Get Column Number

I also have this problem - i guess there is no answer...

• ###### Re: Get Column Number

I had a similar problem when i stumbled upon this topic. Testing this I figured out following workaround which allows the  use of dynamic names of expressions in a chart being used for other calculations in similar chart:

\$(=chr(91) &'\$(=XY)'  & chr(93))

instead of XY insert the expression of the column label which should be used.

• ###### Re: Get Column Number

Thank you Stefan!  Your formula can get a bit messy, but it really works!  Thank you!  I siplified it a little and it seems to work, just replace 'XY' with your desired column's label formula.

\$(= Chr(91) & 'XY' & Chr(93) )

• ###### Re: Get Column Number

Similar to what Stefan Kunte says, I'm using the same expression as in the title to reference it.

For example, I've got the following 3 expressions:

The first two have their names dynamically calculated depending on the user's selections. The third one will calculate the substraction of the second over the first.

I can't use Column(1)-Column(2) since they will be conditioned. And I can't reference them plainly since they're going to be conditioned.

So I'm using the following expression. Blue color means the same as in the expression title. Red color is what it's added:

[\$(=only({<CodInstalación={\$(vInstalacionSel)}>} Instalación))]-[\$(=Only(Asesor))]

Hope it helps!

• ###### Re: Get Column Number

I learn something new every day doing this gig.  Thanks Gysbert for contributing so much to this forum.  You truly are an MVP!

• ###### Re: Get Column Number

will this work ColumnNo([Total]) ?

• ###### Re: Get Column Number

Nope.