Show/Highlight different month groups in line chart?

[Aag]

Hello all,

I have a line chart displaying all months of 2020 for "Sales" measure. My user wants has 2 date ranges - a) Feb to Apr and  b) Sep - Nov.. These 2 timeframes need to be focused along the 2020 line chart 

a) What is the best way to highlight those portions of time frames? Can a different color be used? and can we just average sales of that selected interval?

Let me know if this is possible.. Or even if there are any suggestions on how to achieve this, very much appreciate your ideas

[MayilVahanan]

Hi @Aag 

For change the line color, you can try like below.

If always you need to highlight the same date range, you can create a flag and use in the expression. 

Else, you can use match() function to highlight the month and provide the color code. 
Suppose , if its dynamic, you can write ur own expression in the color.

For sample, 

if(Match(Month, 'Jan', 'Mar', 'Dec'), lightGreen(), lightblue())

[Aag]

Thanks a lot @MayilVahanan  for your quick response. This is great tip!! 

Lets say if I have to show Jan 15 - April 10 in one color and  April 11 - Oct 5 in another color..  Can you please suggest how I should have my axis other than just month? 

Wondering if it is possible to do average of just the colored time frames.. @sunny_talwar 

[MayilVahanan]

Hi @Aag 

If its date wise comparison, you can use date as X-axis to see the color difference correctly. And also, if you want to display avg for few dates, you can try like below

If(Date >= '2020-01-15' and Date <= '2020-04-10', Avg(Sales),Sum(Sales))

Change the date format based on ur data model.

[Aag]

Awesome @MayilVahanan !! Thanks again!!  Can you please let me know the syntax for giving mutiple date 

2020-01-15 to 2020-04-15 - Red color and Avg(Sales) 

2020-07-15 to 2020-09-20 - Blue color and Avg(Sales) 

If none of above satisfy default color is Grey/Black

Can we do a case statement?  How can we use below logic to include color coding as well.. and both the conditions together?

If(Date >= '2020-01-15' and Date <= '2020-04-10', Avg(Sales),Sum(Sales))

If(Date >= '2020-07-15' and Date <= '2020-09-20', Avg(Sales),Sum(Sales))

Sorry for asking multiple questions.. I think this above question solution should solve my issue. If you think this should be in separate thread, please advise. I will create a new question. 

[MayilVahanan]

Hi @Aag 

Try like below

If((Date >= '2020-01-15' and Date <= '2020-04-10') or (Date >= '2020-07-15' and Date <= '2020-09-20'), Avg(Sales),Sum(Sales))

[Aag]

Hi @MayilVahanan  

I created a flag with this condition If((Date >= '2020-01-15' and Date <= '2020-04-10') or (Date >= '2020-07-15' and Date <= '2020-09-20'), Avg(Sales),Sum(Sales))  

In Color expression, when I try to give this expression,  it does not apply this color to that date range. What am I missing below? 

If(flag = 1,lightred(),lightblue()) 

[MayilVahanan]

Hi @Aag 

Its working fine for me. Can you share ur sample app?

[Aag]

Sorry @MayilVahanan  for late response. 

I am still struggling with one issue..  As I mentioned in the beginning, I have multiple date ranges..  and I need different color for these time periods..

When I am giving multiple conditions for the date.. I am not able to get the different colors.. 

Can you let me know what is the mistake in this condition and advise? 

if (date >= '2020-03-01' and date <= '2020-09-01',LightRed()) and if (date > '2020-09-01' and date <= '2020-10-01',LightBlue()) and if (date >'2020-10-01' and date <= '2020-10-15',LightGreen())

rest all months should be in grey if nothing satisfies.. 

I tried the above condition with OR also. but still the above condition does not work for multiple time frames. 

[MayilVahanan · Accepted solution]

Hi @Aag 

Try like below

if (date >= '2020-03-01' and date <= '2020-09-01',LightRed(), 

if (date > '2020-09-01' and date <= '2020-10-01',LightBlue(),

if (date >'2020-10-01' and date <= '2020-10-15',LightGreen(), LightGray())))