color by expression - QlikSense

[#1]

Hi all,

i am struggeling with a simple line chart. It shows revenue over month - so "Month" would be one dimension. It has two expressions. The one calculates revenue for the current year. The other calculates revenue for the previous year. All no problem.

What i want - and until now couldnt get to: I want the line representing the current year to have a specific color (lets say, red) and the line representing the previous year to have another specific color (lets say, gray).

Can anyone help and explain how this can be done in SENSE ?

Thanks!

K

[Anonymous]

Hi Gal - can you send me a screenshot of the issue?

[#3]

Hi Steve,

Before the selection-

After- (just the Month i chose stay in original color)

  

Thanks a lot,

gal

[Anonymous]

Yes, it will only apply to your current selection. It looks like here you're using Set Analysis to display all the values, independent of any selection made. Selecting the Month of Aug should reduce your line chart to just that month. Perhaps consider creating a Drill-down dimension so that selecting a specific Month will then drill down to the Dates within that Month.

If you use the Date functions (such as MonthName()) on your dates this will also correct your sorting issue (which is currently alphabetical).

[Anonymous]

Hi again Gal,

For your use case I would recommend a different (and simpler) approach than value list. You have two dimensions (Month and Alarm type) and 1 measure (Number of Alarms) so you can achieve the required result using a colour by expression similar to

if([Alarm]=0,    rgb(82,204,82),

if([Alarm]=1,    rgb(250,104,26),

LightGray(120)))

I would also consider using a stacked bar chart so that you can see the overall number of alarms, with a breakdown of which were real / false. In the example I've included you can see I've used a brushing technique so that the month(s) you've selected are highlighted but all months remain visible (but are displayed in LightGray) so that you still see the overall pattern for the year.

I hope this helps.

[#6]

Hi steve,

First, thanks for your help.

2 questions:

1. Do you have any idea how to put set Analysis into the expression of color, then the color will ignore the choice of the model?

2. The case that I raised here is an example, the real model can not publish.

In the real model I have two measures, when ValueList is 1 this makes a total of one field and when the ValueList is 2 this makes a total of another field. Do you have any idea for this situation?

Thanks a lot,

gal

[pieterkup]

Hi Gal,

To answer your first question:

I have taken the Alarms app (that Steve created) and added the Set Analysis for the colour coding - but I used the ARGB function for where the months are not in the current selections. With this, you can make the colours for these months slightly lighter, but it will still follow the same colours.

With regards to your second question. You could use the Pick function to change the expression to be used:

=pick([Dimension],sum([field 1]),sum([field 2]))

The Pick function will look in the field [Dimension] for sequential numerical values (starting at 1). According to this value, it will then use the expression in the parameter list, that corresponds to the value of the field [Dimension].

If the values in the [Dimension] is not sequentially numeric, then you could list the values with the match function:

=pick(match([Dimension],'Value 1','Value 2'),sum([field 1]),sum([field 2]))

The Match function will return the position number of the corresponding value in the parameter list, of the row value of [Dimension]. Then the Pick function will then use this position number to choose the corresponding expression.

Just be careful with mixed case values. For this there is the MixMatch function or change the case to UPPER([Dimension]) or lower([Dimension]).

I hope this helps.

Regards

Pieter

[#8]

Hi Pieter,

I will try it.

thanks,

gal

[NareshGuntur]

Steve,

This is great.

But can I use this where the variables have the actual expression stored. It doesn't work for me.

if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim1', $(Sales2011),

if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim2', $(Sales2012),

if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim3', $(Sales2013),

if(  ValueList('Dim1', 'Dim2', 'Dim3', 'Dim4')='Dim4', $(Sales2014)

))))

Cheers,

Naresh

[patrickmryan11]

Hi Steve,

I have followed these instructions and have the colors and the measures I want showing. Now, I would like to show the values and the "labels" on each bar as well.  Is it possible to add "Show: Label" or to define the Label to show and where, as part of an Expression?  Similar to adding ", rgb(192,0.,0)"

Pat

[Anonymous]

Hi Patrick, I don't understand the final part of your request but you can add value labels in the Presentation section.