How to reference a column by its label name

[ken4runner]

In a straight table chart, a new column can be created on the fly and an expression written to define what data will be shown in that column in the table chart when it is rendered; then a label for the column can be given.   In this example the column does not exist in the data model and was created in the table by an expression. Lets call this column: Column1

Is there any way for anther column, called Column2, in that same table to reference Column1, and use the value for Column1 in its own expression? If so, what is the syntax for this? 

[Carlos_Reyes]

It is similar to QlikView. 

https://community.qlik.com/t5/New-to-Qlik-Sense/Calculation-between-columns-in-a-QlikSense-table/td-...

[ken4runner]

 thanks Carlos for the link, I have tried this several times, several different ways - but it does not like the syntax and I cannot get it to work. With or without brackets around the label, which is the only way to reference this item since it is not in the datamodel and I am unable to get Qlik Sense to allow me to use the label name in a different  expression in another column, in the scenario I described in my initial post. 

[Carlos_Reyes]

Well... in the link, the person that created the thread says that he thought it was not working because the syntax checker marks the expression as incorrect... and that still happens to me too, but the expression works. A sample app is enclosed.

[ken4runner]

Hi Carlos, thanks for the example. It looks like it works as a MEASURE, but not as a DIMENSION.  I had been trying this only as a dimension in my testing. When a column is added in a table chart, these are the 2 options: dimension and measure. The expression editor indicates an error for the  exact same expression in both, but it works  (the expression calculates using the label of the other column) as a measure but fails to work as a dimension, and it returns a null value. At least that gives me a workaround, although it is inconsistent and undocumented.

Addendum -- it looks like it only works for measures. The previous column with a label defined must be a measure, and the subsequent column that references it also must be a measure.  If a dimension is used in either place, it does not work at all. This is very odd behavior.

[Carlos_Reyes]

In my humble opinion, it makes sense that you may reuse a measure calculated in another column as reference for further calculations or to make your formula simpler... but in the case of dimensions, it's probably tricky since the dimensions are used in the chart/table to aggregate the data that the measures will use. I guess that's why it's not supported to reference "calculated" dimensions in the measure's definition... although you can create calculated dimensions and they affect the measures calculations perfectly.

In the end, the easiest and most efficient solution is to create the field you need in the data model so that you don't have to mock up aggregation fields directly in your charts, that's not efficient regarding performance.

[ken4runner]

Hopefully having this documented in a community post will help others understand this odd behavior about dimensions in a straight table. Calculated Columns that are Dimensions cannot be referenced by  any other column expressions by label name. Calculated Measures can be referenced in other calculated measures by label name, but they cannot be referenced by a calculated dimension.