topic Re: Bar graph coloring not applying appropriatly based on expression in QlikView

Bar graph coloring not applying appropriatly based on expression

Fri, 29 May 2015 16:00:25 GMT

I've got a stacked bar chart that I want to display, by quarter, sales for the past 2 years.

The issue I have is, coloring applies correctly to only the Year & Quarter I have currently filtered on.

How do I edit my coloring expression to fix that? Is it a set analysis issue?

variables set,

cy = max(year)

pv = max(year)-1

Dimensions:

=right(year,2)&'-Q'&qtr

=1 ---- >I have to add this obligatory dimension to make it a stacked bar, rather than a side by side bar

=category

Expression:

sum ({$ <year+={$(cy),$(pv)}, qtr+={*}>} sales) / sum ({$ <year+={$(cy),$(pv)}, qtr+={*}>} TOTAL <year,qtr> dkey)

Bar chart Color Expression:

pick( match(category,'1','2','3','4'),

RGB(84,130,53),RGB(101,156,64),RGB(121,187,76),RGB(145,224,92))

Re: Bar graph coloring not applying appropriatly based on expression

sunny_talwar — Fri, 29 May 2015 16:05:14 GMT

Can you try changing your expression to this:

=Sum({<year={$(cy),$(pv)}, qtr =>} sales) / Sum({<year={$(cy),$(pv)}, qtr =>} TOTAL <year,qtr> dkey)

Not sure if it will make any difference, but lets start with making that change and see if it works or not.

Best,

Sunny

Re: Bar graph coloring not applying appropriatly based on expression

Fri, 29 May 2015 16:09:28 GMT

Returns same data, but does not resolve coloring issue

If no year filter, all 8 bar stacks are colored appropriate

If 2014 filtered on, 13 & 14 display....but only 2014's bars are colored appropriately.

Re: Bar graph coloring not applying appropriatly based on expression

sunny_talwar — Fri, 29 May 2015 16:11:38 GMT

Give this a shot for color expression:

=Pick(Match(Only({1} Category),'1','2','3','4'),

RGB(84,130,53),RGB(101,156,64),RGB(121,187,76),RGB(145,224,92))

Re: Bar graph coloring not applying appropriatly based on expression

rwunderlich — Fri, 29 May 2015 16:12:51 GMT

Can you post a qvw?

Re: Bar graph coloring not applying appropriatly based on expression

Fri, 29 May 2015 16:20:19 GMT

no year qtr filter I show 8 bars colored correctly

year filtered on shows no data

qtr filtered on shows data appropriatly

Re: Bar graph coloring not applying appropriatly based on expression

Fri, 29 May 2015 16:20:52 GMT

I deal with sensitive data, I get real iffy about posting anything online. =\

Re: Bar graph coloring not applying appropriatly based on expression

sunny_talwar — Fri, 29 May 2015 16:23:03 GMT

Have you tried this both your current expression and the new expression I gave you in my first post? i.e with:

1) sum ({$ <year+={$(cy),$(pv)}, qtr+={*}>} sales) / sum ({$ <year+={$(cy),$(pv)}, qtr+={*}>} TOTAL <year,qtr> dkey)

and

2) Sum({<year={$(cy),$(pv)}, qtr =>} sales) / Sum({<year={$(cy),$(pv)}, qtr =>} TOTAL <year,qtr> dkey)

Re: Bar graph coloring not applying appropriatly based on expression

Fri, 29 May 2015 16:27:05 GMT

Tried both of those. Data displays correctly

Colors correctly for either data filtered on, or when no filter

Re: Bar graph coloring not applying appropriatly based on expression

sunny_talwar — Fri, 29 May 2015 16:29:07 GMT

Colors correctly for either data filtered on, or when no filter

Not sure I understand what you are trying to say here? Can you please elaborate.

Best,

Sunny

Re: Bar graph coloring not applying appropriatly based on expression

Fri, 29 May 2015 16:33:02 GMT

Using both of your codes,

the bar graphs display appropriately no matter what year/qtr is filtered on.

the bar graphs color as I want, when no filter is applied

When a year or qtr filter is applied, only those bars on the graph color correctly, all other bars revert to default color scheme of qlik

Re: Bar graph coloring not applying appropriatly based on expression

sunny_talwar — Fri, 29 May 2015 16:37:16 GMT

So basically adding Only({1} to your color expression did nothing. Everything still stays the same. I am not sure what could be going wrong here. I know you don't want to share your application, but you might want to share a sample of hide sensitive information and then share your application.

Sunny

Re: Bar graph coloring not applying appropriatly based on expression

rwunderlich — Fri, 29 May 2015 16:54:13 GMT

We all deal with sensitive data See: Preparing examples for Upload - Reduction and Data Scrambling

Another alternative is to prepare a small example with dummy data that recreates your problem.

-Rob