topic Re: Losing colour in chart when I add a second expression in QlikView

Losing colour in chart when I add a second expression

Mon, 24 Feb 2014 11:43:53 GMT

I have a 2 dimensional combo chart in Qlikview. First dimension is department, second is financial year.

I have an expression to show number of hours worked by department members, this is represented as a grouped bar on the combo chart, so for each dept, we get a coloured bar for each financial year, as expected.

However, when I add in a second expression for a symbol on the chart, the bars all change to blue. this is rather confusing as it no longer matches the colour coding on other charts. This new expression uses AGGR to calculate a single average for each Dimension 1 value (department) rather than doing a separte value for each combination of dept and financial year.

Can anyone suggest a way to keep the colours of Dimension 2 on the first expression when I add this second expression?

Many thanks

Steve

Re: Losing colour in chart when I add a second expression

Mon, 24 Feb 2014 12:07:03 GMT

Hi,

have you already tried going to Properties and in the Color tab check Multicolored?

Kind regards,

Nuno

Re: Losing colour in chart when I add a second expression

Mon, 24 Feb 2014 12:11:52 GMT

Hi Nuno, thanks for rewsponding.

The Multicoloured box is greyed out, and doesn’t respond when I click on it.

Steve

Re: Losing colour in chart when I add a second expression

martinpohl — Mon, 24 Feb 2014 12:15:51 GMT

add

color(FieldIndex('Dim',Dim))

as the background colour of all of your formulas

while Dim is the dimension of your chart.

The only effect ist in the formula legend

Maybe the values are unfortunately, but I used them in another case.

Re: Losing colour in chart when I add a second expression

Mon, 24 Feb 2014 12:21:26 GMT

Then I would set an expression on the "background color" (you can find it when you expand the plus icon on your first expression).

It should be something like this (depending on the years you have):

=if(Financial_Year='2014', color(1), if(Financial_Year='2013', color(2),color(3)))

Hope this helps.

Kind regards,

Nuno

Re: Losing colour in chart when I add a second expression

Mon, 24 Feb 2014 12:30:55 GMT

This is very helpful,, I've used the following expression to get, in this instance, the desired result:

=if ( Calendar.NexusYear ='2013/14', color(1), if(Calendar.NexusYear='2012/13', color(2),color(3)))

This works fine when we have the last 3 financial years selected, but this won't always be the case. However, I'm sure I can define a variable to do the rest of the work or use this in conjuction with Martin's suggestion to get the result I'm after

Many thanks

Steve

Re: Losing colour in chart when I add a second expression

Mon, 24 Feb 2014 12:33:19 GMT

Yes, Martin's suggestion is more progressive.

Glad I could help!

Re: Losing colour in chart when I add a second expression

Mon, 24 Feb 2014 12:38:11 GMT

Thanks Martin, i had a good response from Nuno which has given me a reasonable solution. Your answer seems to have the potential to get the problem sorted without very complex expressions, but I haven't quite got the syntax cracked......

=Color(FieldIndex('Dim',1)) always seems to colour all the bars black, as does =Color(FieldIndex('Dim',Calendar.NexusYear))

Can you provide any extra suggestions on this?

Many thanks

Steve

Re: Losing colour in chart when I add a second expression

martinpohl — Mon, 24 Feb 2014 12:44:24 GMT

fieldindex returns the position value by loading.

this number is used as the color number from the colour tab.

the formula uses the field Dim (without '', = actual Dim in this line) and compares it with 'Dim' (with '', = the column in the table)

hope this helps

Re: Losing colour in chart when I add a second expression

Anonymous — Mon, 24 Feb 2014 13:14:28 GMT

Hi Stephen,

Please try below code in Background color of expression in:

pick(match(Financial_Year, '2013', '2014'),green(), red())

Hope this helps

Nitha

Re: Losing colour in chart when I add a second expression

Mon, 24 Feb 2014 13:15:35 GMT

Hi Martin, thanks for all you're help so far, it's starting to take shape.

I'm noow using the line :

=Color(FieldIndex('Calendar.NexusYear',Calendar.NexusYear))

This is giving me the problem that each financial year is associated with one colour and this does not change according to which years have been selected. So if I select years 2013/14. 2012/13 and 2011/12, the graph at the top of the chart uses colours 1-3 (blue,red & green), but the one using the method above uses colours 6-8 (brown, pink & grey for the same years.

Can you suggest an adjustment I can make to the line above to force it to restrict the FieldIndex value returned so that it takes into account only the ones selected?

If you can't, thanks for helping anyway, I've learned a lot from this and I'll continue working on it.

Re: Losing colour in chart when I add a second expression

martinpohl — Mon, 24 Feb 2014 13:23:40 GMT

Hi Stephen,

do you use the formula in both expressions?

If financial year 2011/12 is the 6. year in your datas it will get the 6. color.

But in a "normal" selection it will the the first dimension and so the first color.

You can also use the function in a chart with only one dimension / formula by adding it in the dimension paramaters.

Re: Losing colour in chart when I add a second expression

Mon, 24 Feb 2014 13:26:38 GMT

Thanks Nitha, this is also very helpful. This demonstrates that another way to do this would be to have a variable along the lines you've suggested but with more years and colours, and use this variable to control the colouring of financial year dimwensions on all the charts that use it

Re: Losing colour in chart when I add a second expression

Mon, 24 Feb 2014 13:33:29 GMT

The colouring is only important in the first expression, which manifests itself as a bar for each dept/fin year combination. The other expression returns only a single value for each dept, so defaulting to a single colour is fine.

Thanks for all your help with this, I really feel I have a few strong leads to work with now.

Regards

Steve

Re: Losing colour in chart when I add a second expression

Mon, 24 Feb 2014 15:33:02 GMT

I have resolved this.

I ended up using a solution along the lines of that suggested by Martin, but using GetFieldSelections rather than Fieldindex.

=Color (((index(GetFieldSelections(Calendar.NexusYear, ',' , 14), Calendar.NexusYear ) -1) /8) +1)

Here, if I have 3 financial years selected (which don't need to be in sequence, The GetFieldSelections function will give me a string like "2008/09,2011/12,2012/13". I then use index to find the position of the financial year I need the colour for and use the remaining addition, subtraction and division to convert the position (1, 9, 17 etc.) to a colour index (1, 2, 3 etc.)

If you're using this, please be aware that if no years are selected, GetFieldSelections returns an empty string. I will need to test GetSelectedCount to check this and if so, use the string "2007/08,2008/09,2009/10......" etc.

Thanks to Nuno, Martin and Nitha, who all planted seeds that allowed me to reach this highly satisfactory solution.

Re: Losing colour in chart when I add a second expression

stantrolav — Fri, 11 Jul 2014 09:16:05 GMT

Good solution. Respect!