topic Re: How to create multiple text values on Bar Chart in QlikView

How to create multiple text values on Bar Chart

Tue, 11 Sep 2012 12:53:23 GMT

I keep getting asked this question and have been unable to find an acceptable working solution.

We have a bar chart that has two dimensions (entity and rating). We have one expression for the count. The bar charts are stacked. I've attached a picture for reference.

The question we keep getting asked is can we have the percentage relative to one of the ratings along with the total number. In other words, if entity "ABC" has 100 Pass ratings and 5 Marginal Rating and 10 Fail Ratings, the user would like to see the total count and the percentage of fails next to each bar on the chart. So the display for this entity would show for example "115 / 8.70%".

Obviously, we can create tables of numbers that show this information, but the users want the visual appeal of the chart with the number right there and not have to look around or try to associate line 1 with line 1 from a text grid, etc...

Re: How to create multiple text values on Bar Chart

Tue, 11 Sep 2012 13:24:10 GMT

This isn't exactly what you are looking for, but it may help in the meantime. Put the formula for failure percentage as an expression and select 'Invisible'. Select 'Text as Pop-up'. The percentage will show when you hover over the selection. Here is an example.

Re: How to create multiple text values on Bar Chart

whiteline — Tue, 11 Sep 2012 13:40:30 GMT

You can use dual() function for that.

For numeric part use your count expression and for text part - construct the desired string.

Re: How to create multiple text values on Bar Chart

Wed, 12 Sep 2012 12:33:03 GMT

Can you reply with a working example of this dual function in a chart expression? The help system only shows the dual function being utilized in a load script and that is not what we are asking for.

Re: How to create multiple text values on Bar Chart

Anonymous — Wed, 12 Sep 2012 13:21:56 GMT

In addition to the other ideas mentioned, a simple way is to just concatenate manually the expression to receive the percentage after the expression used for the count. So something like:

=count(field)&' / '&(sum(field/field)*100)&'%'

Re: How to create multiple text values on Bar Chart

Wed, 12 Sep 2012 16:18:25 GMT

Have you tested this solution to make sure that it will actually work?

==================================================

In addition to the other ideas mentioned, a simple way is to just concatenate manually the expression to receive the percentage after the expression used for the count. So something like:

=count(field)&' / '&(sum(field/field)*100)&'%'

Re: How to create multiple text values on Bar Chart

Anonymous — Wed, 12 Sep 2012 16:20:18 GMT

Yes, I did.

Have you?

Re: How to create multiple text values on Bar Chart

Wed, 12 Sep 2012 16:46:34 GMT

I modified my expression in my chart very simpy by adding a string to the end of it similar to what you propose...

My current expression is:

Sum ([Sample Count])

I modified it to simply be:

Sum ([Sample Count]) & 'XXXX'

And the result is my chart no longer display any data. I get the "No data to display", so I'm not sure how you made a stackable bar chart work with that expression you posted.

Re: How to create multiple text values on Bar Chart

whiteline — Wed, 12 Sep 2012 17:01:16 GMT

Hi, bneumeier.

Sorry, I've tested dual() expression with bar chart and it works in many cases except stacked mode

You can easily test it yourself:

=dual('test ' & Count(YourValue), Count(YourValue))

But you can use 'text on axis' instead 'values on data points' if its suitable.

Re: How to create multiple text values on Bar Chart

whiteline — Wed, 12 Sep 2012 17:05:11 GMT

Sum ([Sample Count]) & 'XXXX' << its a string

You can't plot string value on numeric axis

Re: How to create multiple text values on Bar Chart

Wed, 12 Sep 2012 17:08:37 GMT

That's what I thought also, but the individual said he tested it

=======================

Sum ([Sample Count]) & 'XXXX' << its a string

You can't plot string value on numeric axis

Re: How to create multiple text values on Bar Chart

Wed, 12 Sep 2012 17:12:37 GMT

So to be clear - if I use dual in the expression and turn the option for "Text on Axis" on the stacked bars will appear correctly, the text will just be outside the chart on the axis - correct?

If that is correct, it might be an option, they will not like it, but we might be able to sell it as am option for a little while.

Re: How to create multiple text values on Bar Chart

Wed, 12 Sep 2012 17:18:08 GMT

I am reporting the problem with Dual as a bug, but unsure what kind of response I will receive

Re: How to create multiple text values on Bar Chart

whiteline — Wed, 12 Sep 2012 17:32:33 GMT

yeah, you right about axis text. Also its grayed. And you have to play with formats to show what you want.

Anyway, it will hepl the users to see what they want.

I think it's a complex question whether it a bug or 'inimplementation' or feature.

Consider that in stacked mode QV shows Total value for a stack, while when you type the expression you do it for the entire dimension (don't know how to say it more clear).

Anyway, I hope it helps you.

Re: How to create multiple text values on Bar Chart

Wed, 12 Sep 2012 18:08:34 GMT

I changed the expression to utilize Dual and then changed to "Text on Axis". Still did not get the text, instead received individual counts for each dimension. This will not work. They want to see the total count and the percentage for a specific dimension relative to the count and not have to calculate it by hand. They want this text to be on/related to each bar.

Re: How to create multiple text values on Bar Chart

Anonymous — Wed, 12 Sep 2012 18:28:27 GMT

Okay, I did a demo test and ran into the same problem....so without directly addressing the issue for the stacked bar chart - I found a working way to achieve the same thing (with the stacked bar chart).

-Create the same expression your trying to use to get the stacked bar so in this case sum([Sample Count]) & 'XXXX'

-make the expression invisible

-select "Text on Axis"

This should allow your stacked bar to appear as it did before while adding your (multiple) data values pertaining to the bar along the axis.

Got to say that I'm not totally satisfied with the design if you have a long value to display but it works and has potential to be formatted to a more polished presentation. Either way, sorry for the confusion.....the point I was driving at was that while you try to figure out the more complex methods that generate the solution you need, you can serve up a working solution to the end-user with a "work-around", if you will, that achieves the same effect by using a simplier method. Learned this from an example where it was requested to display the customer's id next to the customer dimension, possibly in rollover pop-up or as another dimension. This was going to cause either a great burden on maintenance (popup) or exceed the working display area that I had to present the chart....so I just put the customer's id as a tag along to the dimension i.e. Customer&' - ('&CustomerId&')'

Anyway, hope that helps.

Re: How to create multiple text values on Bar Chart

Anonymous — Wed, 12 Sep 2012 18:37:25 GMT

See attachment

Re: How to create multiple text values on Bar Chart

whiteline — Wed, 12 Sep 2012 19:23:07 GMT

2 bneumeier:

You're right. It adds something strange 'on axis' in stacked mode. Sometimes values from one level and somtimes from other.

I've managet to achive the same results as with "Text on Axis" but with 'Values on data points' (see the above post about adding invisible expression).

The problem is that you can't calculate it through every value of the 'stack'. Sometimes it takes one value from top and some times another from bottom. So unfortunately you can't calculate the ratio and the value of each part as you want.

I think this is another reason why QV overrides text representation of expression by default and displays only total amount for a stack bar.

Re: How to create multiple text values on Bar Chart

Anonymous — Wed, 12 Sep 2012 19:31:00 GMT

I think I'm missing something.....

When you create the invisible expression to appear as "Text on Axis", I am under the impression that you are calculating the provided expression expression against the dimension and displaying the resulting value along the axis.

Is this not the case?

Re: How to create multiple text values on Bar Chart

whiteline — Wed, 12 Sep 2012 19:46:02 GMT

can we have the percentage relative to one of the ratings along with the total number

2 autopilot:

You calculate it against one (bar) dimension while the question is how to display the ratio of each stacked part (second dimension).

Your sample is oversimplified, create a stacked bar chart (2 dims), calculate the real sum value (don't substitute it to zero).

Then play with that chart, try to get sum values in text (for each part of stack and entire for the stack).

Additional invisible expression works in both cases whether "Text on Axis" or 'Values on data points'. And works bad.