topic Re: How to color code a column in visual cues in QlikView

How to color code a column in visual cues

Mon, 21 Oct 2013 14:04:08 GMT

Hi ,

I have attached my QVW file, what i am trying to achieve is , I have a column named margin and i am trying to color code the margin based on a formula

i.e. in the visual cues for the upper count i gave it as

=SUM([Sales Margin]) > (SUM([Sales Margin]) / Count(Location))

and for lower count it is

=SUM([Sales Margin]) < (SUM([Sales Margin]) / Count(Location))

basically what i am trying to do is , if the total margin across all locations / no of locations which gives me the avg margin ad if that location margin is greater than avg margin then upper count else lower count.

But it always shows me only upper count?

Can anyone suggest me where i am going wrong?

Thanks

Re: How to color code a column in visual cues

lironbaram — Mon, 21 Oct 2013 14:40:21 GMT

you were close

just change your expression to

=(SUM([Sales Margin]) / Count(Location))

you don't need the first part as it already calculated in the expression

so you just need to enter the value you want the expression to be compared with

Re: How to color code a column in visual cues

Mon, 21 Oct 2013 14:43:25 GMT

Hi Liron,

It worked fine, Thanks

Re: How to color code a column in visual cues

salto — Mon, 21 Oct 2013 14:44:37 GMT

Hello,

have a look at the count(total distinct Location) and sum (total [Sales Margin]) formulas, they do what you need!

Hope this helps.

Re: How to color code a column in visual cues

Mon, 21 Oct 2013 16:06:54 GMT

Hi Liron,

It works fine however, i need to make a small change to this one, The margin color code shouldnt change if i select any location it should be the same .I know it can be achieved by set analysis, but i am not exactly sure on how do it.Can you help me with any ideas?

Thanks

Re: How to color code a column in visual cues

Mon, 21 Oct 2013 16:07:04 GMT

Hi SALTO,

Thanks

Re: How to color code a column in visual cues

salto — Mon, 21 Oct 2013 16:14:33 GMT

This may help you:

=(SUM({1<Location=>} total [Sales Margin]) / Count({1<Location = > } total distinct Location))

Change the conditions in the visual cues and you are done.

Regards!

Re: How to color code a column in visual cues

Mon, 21 Oct 2013 16:19:37 GMT

Hi SALTO,

it did work fine,Thanks