conditional measure colouring in line chart

[Aditya_Chitale]

Hi All,

I have a line chart in which I am showing pre and post period data based upon month selection.

If my selected month is Apr, pre period would be Jan,Feb,Mar and post period would be May,June,July... upto December. Now I want to color the measure in two different colours. Blue for Pre-Period (Jan,Feb,Mar) and Green for Post-Period (May,June,July,...).

Is there any way to do this in QlikSense ? Hope I am clear with my explaination.

Regards,

Aditya

[edwin]

try something like this, in Appearance:

=if(Date<date('1/10/2022'),red(),green())

just change the expression using your variable that should evaluate to the correct date format

[Aditya_Chitale]

I have used similar expression, but in my case, I have month field instead of date and the pre and post period in it should be dynamic.

Currently, if I am selecting 3 months viz. Mar, Apr,May, my color expression is not working correctly as the values in month field (in your case Date) is not getting captured for pre and post period instead it is returning selected month values

I have used below expression:

if(num([Training start date.autoCalendar.Month]) < num(subfield(GetFieldSelections([Training start date.autoCalendar.Month]),',',1)),
'#0000FF'
,
if(num([Training start date.autoCalendar.Month]) > num(subfield(GetFieldSelections([Training start date.autoCalendar.Month]),',',-1)),
'#00FF00'
)
)

[edwin · Accepted solution]

i was guiding you to a solution.  the solution to the question how to change the color is to create something like the expression i gave you.

you may have 2 issues, 1st (hoping you already solved this), you need to make sure you have a chart that has all the months you want not just the selected months.  2nd is the coloring.

you first need to fix your expression for measure.  the color expression will not limit your data, it is the measure expression that does that.  also i suggest you create a variable(s) (im guessing you need 2)  possibly the lower bound of the selected values and the upper bound and use that in your color expression.

having said that, the following chart uses Month as dimension.  the user selects a range of the Month field.  the variables vLow and VHigh gets the lower and upper bounds of the selection.

the measure is sum({<Month=>}Volume)

the color expression is 

=if(only({<Month=>}Month)<date('$(vLow)'), red(),
if(only({<Month=>}Month)>date('$(vHigh)'), blue(), green()))

so if below the selected months - red, if above Blue, else green

of course this is a simplified version of your problem.  you will have to tweak it based on the profile of your data (like what if other fields are selected that affects the chart, what if other years are available, what if the scope of the chart spans more than 1 year - ie, from June 2021 to May 2022)  without knowing your DM, the specific design solution could change drastically.

[Aditya_Chitale]

Thanks it worked. You were right, the measure expression was limiting my data hence, the color expression was not working properly.

changed measure expression and got below output