Hello,

I was once asked in an interview what my favourite QlikView functions were. My answer didn’t seem to be what they expected, the people asking the question thought the Peek function was one of the best but I stood firm.

I like text functions; Left, Mid, Right, Index, Replace, Substring, etc.

I was even told at this point that these functions are old and not as new as their preferred Peek. I did point out that Peek was available on my first computer (ZX Spectrum)

(The command is there!!!)

The reason I bring this up is because you can do so much with text functions. Here I will use the index function to highlight bars in a bar chart

Charts allow users to select information in QlikView. Click on a bar and the data reduces. We don’t want this to happen here and we shall use set analysis to keep the chart displaying all the bars.

The expression sum({<Month=>}[Purchase Price]) would still show all the months data within the data set even when the user has selected a single month

QlikView Bar Chart

This works well although I want to make it clear to the user that the Month of Jan has been selected, the chart will help me with this as its big, bold, and prominent! By changing the colour of the bars to match the months that have been selected it will be a great visual cue.

Each expression has a number of additional attributes you can modify. We want to change the background colour:

QlikView Expression Properties

What we need to do is check if the chart dimension matches the values selected in the Month field. The function GetFieldSelections(Month) will return the selected values of Month so the formula becomes very simple; if(GetFieldSelections(Month)=Month,green()) .

This works well when a single month is selected. If ‘Jan’ is selected GetFieldSelections(Month) will return ‘Jan’. The conditional requirement is met and we change the background colour to green.

Unfortunately this doesn’t work if two or more values are selected. If the user has selected Jan & Feb then GetFieldSelections(Month) will return ‘Jan,Feb’ which won’t work. What we need to do is to get QlikView reading the text for the occurrence of either Jan or Feb. One of the ways this can be achieved is by using the Index() function.

Index(1,2,3) has three attributes.

1. The text you will read
2. The text you want to find
3. The starting point which you will read from (Optional – if left out it will read from the beginning)

What you get back is a number which is the starting position of the first instance of the text you were looking for or zero if the text wasn’t found. For example Index(‘Jan,Feb’,’Feb’) will return 5 and Index(‘Jan,Feb’,’Mar’) will return 0 (zero).

So now rather than looking for a specific month we can use this function to be either =0 (not found) or >0 (found).

QlikView Bar Chart Expression Background Properties

Here the background properties uses the expression: =if(index(GetFieldSelections(Month),Month)>0,green())

I hope this has been useful, look forward to hearing your comments.

Happy Qliking

Richard