topic Re: (issue) Colour Expression per Category on Multiple Month Bar Chart in App Development

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

riclight89 — Wed, 04 Jan 2023 09:27:53 GMT

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

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

QFabian — Wed, 04 Jan 2023 19:42:47 GMT

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)))))))))))))))))
)

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

QFabian — Wed, 04 Jan 2023 19:42:50 GMT

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)))))))))))))))))
)

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

riclight89 — Thu, 05 Jan 2023 06:10:13 GMT

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

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

QFabian — Thu, 05 Jan 2023 12:57:53 GMT

Excelent!, glad to help