(issue) Colour Expression per Category on Multiple Month Bar Chart

[riclight89]

Hi,

I have a bar chart which calculates the top x brands per month for the last 12 months. I am trying to give these brands a specific colour each using the colour expression:

Sales Expression:

sum({$<CreateYear=, CreateMonth=, CreateYearMonth = {">$(=Date(Addmonths(Date#(max(CreateYearMonth),'YYYYMM'),-12),'YYYYMM'))<=$(=Date(Addmonths(Date#(max(CreateYearMonth),'YYYYMM'),0),'YYYYMM'))"}>}Sales)

 Sales Colour Expression:

if(Category = 'A',RGB(255,0,0),
if(Category = 'B',RGB(0,76,153),	
if(Category = 'C',RGB(0,102,204),	
if(Category = 'D',RGB(220,20,60),
if(Category = 'E',RGB(0,0,255),	
if(Category = 'F',RGB(0,0,128),
if(Category = 'G',RGB(0,128,255),
if(Category = 'H',RGB(160,160,160),
if(Category = 'I',RGB(255,51,51),
if(Category = 'J',RGB(255,215,0),
if(Category = 'K',RGB(255,0,0),
if(Category = 'L',RGB(0,191,255),
if(Category = 'M',RGB(178,34,34),
if(Category = 'N',RGB(139,0,0),
if(Category = 'O',RGB(0,110,0),
if(Category = 'P',RGB(220,20,60)
,RGB(200,200,200)))))))))))))))))

result is only the last Bar receiving the colour allocations:

I have tried using the colour expression in the dimension Category Colour expression as well. I'm guessing the multiple month aggregation has something to do with it, checking if there is a solution?

Thanks in advance,

Ric

[QFabian]

Hi @riclight89 , i tried to recreate your data and the chart, and i get this :

When there is a Month or Period selected, that period in chart is painted

So, you need to put your set analysis expression in the color formula too.

{$<CreateYear=, CreateMonth=, CreateYearMonth = {">$(=Date(Addmonths(Date#(max(CreateYearMonth),'YYYYMM'),-12),'YYYYMM'))<=$(=Date(Addmonths(Date#(max(CreateYearMonth),'YYYYMM'),0),'YYYYMM'))"}>}

This is my example, i tried to reduce the expression using variables :

vPeriod12 = =num(addmonths(date(CreateYearMonth, 'YYYYMM'), -12))

vPeriod1 = =max(CreateYearMonth)

then using this chart expression  :

sum({<CreateYear=, CreateMonth=, CreateYearMonth = {">$(vPeriod12)<=$(vPeriod1)"}>} Sales)

and seeing the chart like this, when one month and year is selectd :

Color  expression :

ONLY(

{<CreateYear=, CreateMonth=, CreateYearMonth = {">$(vPeriod12)<=$(vPeriod1)"}>}

if(Category = 'A',RGB(255,0,0),
if(Category = 'B',RGB(0,76,153),
if(Category = 'C',RGB(0,102,204),
if(Category = 'D',RGB(220,20,60),
if(Category = 'E',RGB(0,0,255),
if(Category = 'F',RGB(0,0,128),
if(Category = 'G',RGB(0,128,255),
if(Category = 'H',RGB(160,160,160),
if(Category = 'I',RGB(255,51,51),
if(Category = 'J',RGB(255,215,0),
if(Category = 'K',RGB(255,0,0),
if(Category = 'L',RGB(0,191,255),
if(Category = 'M',RGB(178,34,34),
if(Category = 'N',RGB(139,0,0),
if(Category = 'O',RGB(0,110,0),
if(Category = 'P',RGB(220,20,60)
,RGB(200,200,200)))))))))))))))))
)

[QFabian · Accepted solution]

Hi @riclight89 , i tried to recreate your data and the chart, and i get this :

When there is a Month or Period selected, that period in chart is painted

So, you need to put your set analysis expression in the color formula too.

{$<CreateYear=, CreateMonth=, CreateYearMonth = {">$(=Date(Addmonths(Date#(max(CreateYearMonth),'YYYYMM'),-12),'YYYYMM'))<=$(=Date(Addmonths(Date#(max(CreateYearMonth),'YYYYMM'),0),'YYYYMM'))"}>}

This is my example, i tried to reduce the expression using variables :

vPeriod12 = =num(addmonths(date(CreateYearMonth, 'YYYYMM'), -12))

vPeriod1 = =max(CreateYearMonth)

then using this chart expression  :

sum({<CreateYear=, CreateMonth=, CreateYearMonth = {">$(vPeriod12)<=$(vPeriod1)"}>} Sales)

and seeing the chart like this, when one month and year is selectd :

Color  expression :

ONLY(

{<CreateYear=, CreateMonth=, CreateYearMonth = {">$(vPeriod12)<=$(vPeriod1)"}>}

if(Category = 'A',RGB(255,0,0),
if(Category = 'B',RGB(0,76,153),
if(Category = 'C',RGB(0,102,204),
if(Category = 'D',RGB(220,20,60),
if(Category = 'E',RGB(0,0,255),
if(Category = 'F',RGB(0,0,128),
if(Category = 'G',RGB(0,128,255),
if(Category = 'H',RGB(160,160,160),
if(Category = 'I',RGB(255,51,51),
if(Category = 'J',RGB(255,215,0),
if(Category = 'K',RGB(255,0,0),
if(Category = 'L',RGB(0,191,255),
if(Category = 'M',RGB(178,34,34),
if(Category = 'N',RGB(139,0,0),
if(Category = 'O',RGB(0,110,0),
if(Category = 'P',RGB(220,20,60)
,RGB(200,200,200)))))))))))))))))
)

[riclight89]

@QFabian worked perfectly (even without creating the variables). Thank you!

[QFabian]

Excelent!, glad to help