topic Re: Detecting current column in Expression Text Colour in QlikView

Detecting current column in Expression Text Colour

Anonymous — Mon, 15 Oct 2012 10:48:43 GMT

Hi,

Having found out that the Visual Cues are not able to process expressions on the chart's row level I am now setting up colours under the Expression's ( + ) symbol.

I would like to still have the sort of flexibility Visual Cues provides where I don't need to reference the calculation I am testing against. What I mean by this is I would like to reference the column value in the simplest way possible.

I realise I can use with the Column(ColumnNo) syntax, or the Columns label, but neither of these fits my purposes. I am setting up the various columns to not be calculated and hidden, based on user button selections and the available data, making tests for Column(ColumnNo) is a problem. As my column labels are dynamically set with date ranges and notification of their being "Full Month" or "MTD" etc, using column labels is also not an option.

Is there a way to test for the value in the column I am currently changing the text colour of. I have tried Column(ColumnNo()) as I understand ColumnNo() should return the position of the column, working from left to right. However, I can't get the ColumnNo() function to return me the position of the table's column using the expression "=ColumnNo()" in either a pivot or a straight table.

Any thoughts ?

Jonathan

Re: Detecting current column in Expression Text Colour

swuehl — Mon, 15 Oct 2012 12:12:59 GMT

I believe ColumnNo() will only work in a pivot table, not in a straight table, and it will require horizontal dimensions (i.e. row segments to work on).

I think you should be fine using column labels, referencing your dynamic label expressions like

="$(=DynamicLabelExpresssionComesHere)"

Re: Detecting current column in Expression Text Colour

Anonymous — Mon, 15 Oct 2012 13:22:07 GMT

Thanks. Turning the column labels into variables offers me a way forwards.

Jonathan

Re: Detecting current column in Expression Text Colour

Anonymous — Tue, 16 Oct 2012 07:02:30 GMT

Hi,

Seems that following your example wasn't as powerful as I hoped. I was expecting this syntax to allow me to:

1. Reference the current column from within it's Text Colour expression

2. Use the column label in other expressions to calculate MTD vs. LMTD etc comparisons

Am I doing this correctly. I set up a variable called:

ColMTD

Using this formula:

='MTD to '&MTDEnd

To create a label that looks like this:

MTD to 16/10/2012

I then reference this within it's own Text Colour using the following syntax:

=if($(=ColMTD)<0,red())

This doesn't work for me though. I have also tried with quotes around the $(), not having the equals sign in the $() and removing the $() expansion entirely. I have also tried these tests when assigning the ColMTD within another field, and not just i the TextColour expression.

Am I missing something ?

Jonathan

Re: Detecting current column in Expression Text Colour

Anonymous — Tue, 16 Oct 2012 07:29:46 GMT

Seem to have a syntax that works now, though it's pretty messy:

$(='['&ColMTD&']')

Appears that the square brackets are needed, due I assume to the spaces in the label text.

Jonathan

Re: Detecting current column in Expression Text Colour

swuehl — Tue, 16 Oct 2012 08:49:00 GMT

Right, the square brackets are needed, so you need these or double quotes like I tried to indicate above.

And you can probably simplify your syntax slightly to

[$(ColMTD)]

like in a e.g. background color expression:

=if( [$(ColMTD)] < 0, lightred() )

=if( "$(ColMTD)" < 0, lightred() )

Re: Detecting current column in Expression Text Colour

Anonymous — Tue, 16 Oct 2012 12:59:51 GMT

Hi again,

I think I tried the single quotes initially. Now that I have this new syntax it is a lot cleaner.

Many thanks !!!

Jonathan

Re: Detecting current column in Expression Text Colour

Tatynout — Mon, 27 Jul 2020 16:09:53 GMT

Hello Swuehl,

Did this syntax changed with Qlik Sense last upgrade? I tried this but it does not work.

Thanks for your reply.

Regards,

Tatiana