Different colors for Bars in combo chart with a conditional check on dates

[shraddha6369]

Hello,

In my app I am trying to compare my field [Invoice Date] with a string  like this('10/17/2017') in the Appearance-Color and Legend ( custom color, color by expression ,expression as color is selected ) for a combo chart.

if([Invoice date]>='10/17/2018','green','blue') 

I do see a color change for bar (which has Dimension----[Invoice date.autocalender.MonthYear]). But when i do incremental load for some inserted records, those records on the bar does not show colors according to the condition. Inserted dates are greater than the static mentioned date(10/17/2018).

If I add [Invoice Date] as a field in table and check if([Invoice date]>='10/17/2018','green','blue')  for text color, my dates are appearing  in proper color (all the dates greater than 10/17/2017 are in green text color ).

Any help with this is much appreciated.

Thanks 

[hic · Accepted solution]

I suspect that there are several Invoice dates per bar. If so, your expression

if([Invoice date]>='10/17/2018','green','blue') 

will be interpreted as 

if(Only([Invoice date])>='10/17/2018','green','blue')  which is the same as  if(NULL>='10/17/2018','green','blue') 

I suggest you ask yourself what the condition should be if there is more than one date in the bar: Should you compare '10/17/2018' with the largest date in the group? Or perhaps the smallest? Once you have answered that, you'll know which of the following to use:

if(Max([Invoice date])>='10/17/2018','green','blue') 

if(Min([Invoice date])>='10/17/2018','green','blue') 

See also https://community.qlik.com/t5/Qlik-Design-Blog/Use-Aggregation-Functions/ba-p/1475833

Good luck

HIC

[shraddha6369]

Thanks for your quick response to this. Just had a doubt why the same condition is giving proper results

---

@hic wrote:

I suspect that there are several Invoice dates per bar. If so, your expression

if([Invoice date]>='10/17/2018','green','blue') 

will be interpreted as 

if(Only([Invoice date])>='10/17/2018','green','blue')  which is the same as  if(NULL>='10/17/2018','green','blue') 

I suggest you ask yourself what the condition should be if there is more than one date in the bar: Should you compare '10/17/2018' with the largest date in the group? Or perhaps the smallest? Once you have answered that, you'll know which of the following to use:

if(Max([Invoice date])>='10/17/2018','green','blue') 

if(Min([Invoice date])>='10/17/2018','green','blue') 

See also https://community.qlik.com/t5/Qlik-Design-Blog/Use-Aggregation-Functions/ba-p/1475833

Good luck

HIC

---

in table ? but it did solve my problem in bar chart.

Thanks again.