Color by expression in Line chart for top 5 Market

[ArchanaB]

Hi Everyone,

I am working on one Line chart which will display top 5 market sale by Months. I could get top 5 ,market sale by giving fixed number as 5 in Market dimension. I want to give custom color to each market from that top 5 market. 

I am doing Color by expression in color and Legend part but it doesn't display single color through out the line it shows gray line in between . It looks like as below screenshot.   I need some expert guidance for the same.

Expression used for color by expression  - 

if (LVL4_MARKET_DISPLAY = Aggr(if(Rank(sum({<LVL4_MARKET_DISPLAY-={'-1: UNKNOWN'}>}FTE))=1,LVL4_MARKET_DISPLAY),LVL4_MARKET_DISPLAY), '#4e9e45',
if( LVL4_MARKET_DISPLAY = Aggr(if(Rank(sum({<LVL4_MARKET_DISPLAY-={'-1: UNKNOWN'}>}FTE))=2,LVL4_MARKET_DISPLAY),LVL4_MARKET_DISPLAY), '#9fd142',
if( LVL4_MARKET_DISPLAY = Aggr(if(Rank(sum({<LVL4_MARKET_DISPLAY-={'-1: UNKNOWN'}>}FTE))=3,LVL4_MARKET_DISPLAY),LVL4_MARKET_DISPLAY), '#35786e',
if( LVL4_MARKET_DISPLAY = Aggr(if(Rank(sum({<LVL4_MARKET_DISPLAY-={'-1: UNKNOWN'}>}FTE))=4,LVL4_MARKET_DISPLAY),LVL4_MARKET_DISPLAY), '#204e77',
if( LVL4_MARKET_DISPLAY = Aggr(if(Rank(sum({<LVL4_MARKET_DISPLAY-={'-1: UNKNOWN'}>}FTE))=5,LVL4_MARKET_DISPLAY),LVL4_MARKET_DISPLAY), '#aa198d')))))

Thank you in advance.

[Primrose]

Hi @ArchanaB,

Do you need the colors to be estimated or you may assign a specific color to each market? If the second is an option, I would recommend you to create a master dimension for the markets and assign a color for each one.

[ArchanaB]

Hi,

I cant use master dimension for each market, there are 100+ markets. 

In graph , I am displaying top 5 market .  and I would like to set color for those top 5 market only . meaning, for highest market color =green , 

second highest color= blues, 

third highest color = yellow . That way

[Primrose]

I see. It looks from the screenshot that the color is estimated only for Aug and not for the other months. Can you add the month dimension to the expression?

[ArchanaB]

Hi ,

the month dimension is already added. This graph displays top 5 market for all 12 months. For Jan , Feb. and other months the FTE is 0 and only it has data for Aug and Sep month , that's why the graph shows that way.

The main issue is the color, it does gives the color as per expression but it is not constant for whole line. It has gray areas.

[Primrose]

@ArchanaB , can you play around with adding the month in the color expression? Something like that:

if (LVL4_MARKET_DISPLAY = Aggr(if(Rank(sum({<LVL4_MARKET_DISPLAY-={'-1: UNKNOWN'}, Month>}FTE))=1,LVL4_MARKET_DISPLAY),LVL4_MARKET_DISPLAY, Month), '#4e9e45',
if( LVL4_MARKET_DISPLAY = Aggr(if(Rank(sum({<LVL4_MARKET_DISPLAY-={'-1: UNKNOWN'}, Month>}FTE))=2,LVL4_MARKET_DISPLAY),LVL4_MARKET_DISPLAY, Month), '#9fd142',
if( LVL4_MARKET_DISPLAY = Aggr(if(Rank(sum({<LVL4_MARKET_DISPLAY-={'-1: UNKNOWN'}, Month>}FTE))=3,LVL4_MARKET_DISPLAY),LVL4_MARKET_DISPLAY, Month), '#35786e',
if( LVL4_MARKET_DISPLAY = Aggr(if(Rank(sum({<LVL4_MARKET_DISPLAY-={'-1: UNKNOWN'}, Month>}FTE))=4,LVL4_MARKET_DISPLAY),LVL4_MARKET_DISPLAY, Month), '#204e77',
if( LVL4_MARKET_DISPLAY = Aggr(if(Rank(sum({<LVL4_MARKET_DISPLAY-={'-1: UNKNOWN'}, Month>}FTE))=5,LVL4_MARKET_DISPLAY),LVL4_MARKET_DISPLAY, Month), '#aa198d')))))

I guess, there is something with aggr(). As you mention, the color is not constant for the whole line. If you look at the screenshot again, you may notice that the color is only for Aug (from the middle point between July and August to the middle point between August and September). You may also try with adding year instead of month. Play around and test what works for you. There are a lot of discussions about aggr() and rank() here. Maybe you could check them for inspiration.