topic Re: Multicolor bars in Combo-chart in QlikView

Multicolor bars in Combo-chart

prasannarc_jbs2 — Tue, 20 May 2014 15:56:42 GMT

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

Re: Multicolor bars in Combo-chart

rwunderlich — Tue, 20 May 2014 16:29:20 GMT

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

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

-Rob

Re: Re: Multicolor bars in Combo-chart

prasannarc_jbs2 — Tue, 20 May 2014 17:08:25 GMT

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

Re: Multicolor bars in Combo-chart

rwunderlich — Tue, 20 May 2014 17:38:01 GMT

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

Re: Multicolor bars in Combo-chart

prasannarc_jbs2 — Tue, 20 May 2014 17:52:16 GMT

Sure Rob,

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

Thank you!

Prasanna

Re: Multicolor bars in Combo-chart

prasannarc_jbs2 — Fri, 23 May 2014 15:31:27 GMT

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