Multicolor bars in Combo-chart

[prasannarc_jbs2]

Dear Members,

I am trying to display multicolored bars inside the combo chart having 2 Dimensions(Product & Zone) and 3 expressions.

By using the below expressions in Expression tab -> Background color I got the required results. But if I make any selection to the Dimension e.g. If I select any zone The combo-chart looses all its colors from the bars and set to default color.

1. For one dimension

=pick(match(Mod(RowNo(),18),1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,0),RGB(141,170,203),RGB(252,115,98),RGB

(187,216,84),RGB(255,217,47),RGB(102,194,150),RGB(229,182,148),RGB(231,138,210),RGB(179,179,179),RGB

(166,216,227),RGB(171,233,188),RGB(27,125,156),RGB(255,191,201),RGB(77,167,65),RGB(196,178,214),RGB(178,36,36),RGB(0,172,172),RGB(190,108,44),RGB(105,84,150)

)

2. For multidimension:

=pick(match(Mod(aggr(DISTINCT RowNo(),Product,Zone),18), 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,0), RGB(141,170,203), RGB(252,115,98), RGB(187,216,84), RGB(255,217,47), RGB(102,194,150),RGB(229,182,148), RGB(231,138,210),RGB(179,179,179),RGB(166,216,227), RGB(171,233,188),RGB(27,125,156),RGB(255,191,201), RGB(77,167,65),RGB(196,178,214),RGB(178,36,36),RGB(0,172,172),RGB(190,108,44),RGB(105,84,150)

)

Is there any other way to achieve the same? The only problem with this approach is when I select any Zone The combo-chart looses all its colors from the bars and set to default color. Please find the attached sample qvw and data set for your reference.

Any help would be appreciated.

Many Thanks,

Prasanna

[rwunderlich]

I think your color expression may be more complex than it needs to be. How about:

=color(Mod(RowNo(),18))

-Rob

[prasannarc_jbs2]

Hello Rob,

Thank you for a quick response!

Actually I cannot use the rowno() function without aggr() as I dont want to display Zones where Sales is 0 or Null()

Please see the attached files with comparison between your expression and mine.

Prasanna

[rwunderlich · Accepted solution]

My expression works fine on SR5+. Your aggr() is only required to work around a suppress zero values bug that was fixed in SR5. Update if you can.

-Rob

[prasannarc_jbs2]

Sure Rob,

Currently I am on SR2, I will upgrade it to SR5 and test.

Thank you!

Prasanna

[prasannarc_jbs2]

Hi Rob,

Just now I have upgraded my Qlikview desktop version to 11.20 SR5. You are correct, the issue is no longer exists in this version.

But i believe there could be an alternative way or work around in SR2 version which can resolve the issue.

Thank you so much for taking time and share your thoughts on this issue.

Cheers comunity

Regards,

Prasanna