Stacked Bar Chart - multicolor

[unkisoN]

Dear community,

Following Tables are available:

LOAD * INLINE [
F1, F2, F3, F4, F5, F6
Impl_Year, Impl_Number, SalesPerson, ItemCode, Forecast
2018, 2018_01, John, 100101, 25000
2018, 2018_02, Boris, 100102, n.a.
2018, 2018_03, Pablo, 100103, 20000
2019, 2019_01, John, 100104, 15000
2019, 2019_02, Boris, 100105, n.a.
2019, 2019_03, Pablo, 100106, 7500
];

LOAD * INLINE [
F1, F2, F3, F4, F5
PostingDate, ItemCode, InvoiceNumber, Amount, InvoiceType
01.01.2018, 100101, INV_01, 1550, 1
01.01.2018, 100101, INV_02, 30000, 2
01.01.2018, 100102, INV_03, 25000, 1
01.06.2018, 100102, INV_04, 18555, 2
01.06.2018, 100102, INV_05, 33445, 1
01.06.2018, 100103, INV_06, 25222, 2
01.06.2018, 100104, INV_07, 1520, 1
01.03.2019, 100106, INV_08, 1521, 2
01.03.2019, 100105, INV_09, 14582, 1
01.03.2019, 100103, INV_10, 12345, 2
01.03.2019, 100105, INV_11, 455, 1
];

I have created a pivot table with the following expressions and it works perfect so far.

Expression(Amount):

Sum({$< InvoiceType-={1}>} Amount

Expression (Fullfillment):
if(Forecast = 'n.a.', 'no forecast',
(Sum({$< InvoiceType-={1}>} Amount)/Forecast))

Expression (Classification):
if(Fullfillment= 'no forecast', 'no forecast',
if(Fullfillment > 1.50, '>150',
if(Fullfillment > 1.10, '110-150',
if(Fullfillment > 0.90, '90-110',
if(Fullfillment >= 0.50, '50-90', '<50')))))

Background Color:
if(Fullfillment= 'no forecast', RGB(191, 191, 191),
if(Fullfillment > 1.50, RGB(0,102,255),
if(Fullfillment > 1.10, RGB(0,176,80),
if(Fullfillment > 0.90, RGB(146,208,80),
if(Fullfillment >= 0.50, RGB(255,192,0), RGB(255,0,0))))))

Problem for me now:

I have to create the same Information as a Chart Bar and should look like this:

This is the part where i am struggling right now.

If have different Views, but i think if i can figure this one out, the rest would be easy for me.

Thanks for the help !

[rubenmarin · Accepted solution]

Hi @unkisoN, sample attached, this won't give good performance with too many data, but with few data can work.

To improve performance you can try to precalculate in script, some improvements may also be made without rquiring script precalculations, but first try if it works like this.

[unkisoN]

Thank you very much @rubenmarin