QlikView Tutorial – Vertical labels inside bars

by Stefan Stoichev on September 10, 2010

Yesterday i spotted a question on QlikView Community forum regarding putting dimension labels inside the bars they represent. It was an interesting question because i never had in mind to do such a solution (different customers – different designs :-) ). The “challenge”  was from two parts – put labels inside bars and this label to be on the bottom.

1. Labels in bars (no matter the position)

Lets imagine that the data look like:

[table id=1 /]

And the bar chart will have as dimension “Fruit” and as expression
Dual(Fruit,sum(Qty)) .
In “Presentation” tab in “Values on Data Points”  section check “Vertical” and “Plot Values Inside Segments” and uncheck “Show Legend”. The result is shown bellow:

As you can see the labels are automated positioned in the middle of the bar but when the text is bigger than bar itself no text is shown. The second approach is a little different as realization but will show the text no matter the bar size.

2. Labels in bars (with calculated position)

The data is the same but the chart type is ”Combo Chart”. Dimension is still Fruit and there is two expressions:

- sum(Qty) (set as Bar and unchecked “Values on Data Points”)

- Dual(Fruit, sum(0.1))(set as “Invisible”, “Symbol” and checked ”Values on Data Points”)

The second expression is actually the displayed label. 0.1 in sum(0.1) is the position of the labels ( it is the offset from the 0 of the axis). The label can be extended with number of Qty and the expression will be:
Dual(Fruit & ' (' & sum(Qty) &')', sum(0.1))
And the result chart is:

As you can see all labels and quantity values are shown.

{ 32 comments… read them below or add one }

1 K Smith September 10, 2010 at 19:01

Very nice – I wonder if you can change the font color within the bar(s)?

Reply

2 Stefan Stoichev September 10, 2010 at 19:32

If you expand the second expression there is “Text color” option where you can deffine color. See the link for more info http://goo.gl/E3vR

Reply

3 Pablo Labbe September 14, 2010 at 18:13

Very nice but I think this functionality isn´t explicit defined in Qlikview Reference Manual.

Reply

4 daniel September 18, 2010 at 12:55

:) Pablo,
there are so many posts in the forums and the community site that makes me thanking god that the developers didn’t choose to stay with only what is……explicitly defined in Qlikview Reference Manual.

Reply

5 daniel September 18, 2010 at 12:59

im sorry Stefan, i forgot:
this is some real nice solution. on the border of ingenuity :-)
(now why didn’t i think of that myself lol).

tho i fail to understand why Qliktech didn’t include such a feature in the first place. would be nice if we could concentrate about the real problems of our project an not waste time on finding solutions to things that ought to be there in the first place
just my 2cents lol
Daniel

Reply

6 n.allano March 4, 2011 at 09:14

Hello,
Nice tips!
How would you do if you Fruit Category was a calculated dimension?
How would you write your Dual expression while using the calculated dimension?
I have also posted my question here: http://community.qlikview.com/forums/p/30357/165845.aspx#165845

Thanks!

Reply

7 Nate August 24, 2011 at 23:08

Awesome tip. One question though. Is there a way to hide the (.1) from Excel if you export the graph to Excel? So that you are just left with the SUM and not that?

Reply

8 Stefan Stoichev August 25, 2011 at 00:16

Hi Nate,

it is an interesting question but don’t think there is an easy way to do this. The only thing that i can think of is to use macro to export the chart and then delete the column automatically.

Regards!
Stefan

Reply

9 Nate August 25, 2011 at 16:14

Ok great. Thanks for the help.

Reply

10 Nate August 25, 2011 at 17:07

Actually, one other question. I’ve flipped the chart so the bars are going horizontally. Is there a way then with split axis to place all of the numbers on the far right of the bar graph so they all line up? Currently, I can get the numbers out to the right, but they follow the same curve as a line graph.

Reply

11 Stefan Stoichev August 25, 2011 at 17:30

This can be done :)
In the second (invisible) expression instead sum(0.1) type max(total Qty)
I’ve made a sample qvw file where this is done:
http://dl.dropbox.com/u/63800/labels-inside-bars.qvw
Stefan

Reply

12 Peter May 15, 2013 at 10:17

There is just a problem if you add for example another Banana, 10 to your inline table … then all labels in the chart don’t look nice …

13 Malki September 13, 2011 at 12:30

Hey,
Thanks, this is helpfull.
Can you use the same idea in a Pie chart? the “invisible” option is disabled.
I want to show a dimention, and on the side list the full Sales amount, and on the pai the % only
Thanks.

Reply

14 Stefan Stoichev September 13, 2011 at 14:15

Hi Malki,

unfortunately this approach cannot be used in pie chart. Pies are with a little limited functionality in general.

Stefan

Reply

15 Malki September 14, 2011 at 08:54

Thanks. Is there a way to maybe sign up and get an email everytime you post a new blog?

Reply

16 Stefan Stoichev September 21, 2011 at 10:27

Hi Malki,

im just a guest author and not the owner of the blog. I’ll speak with qvapps guy to see if is possible to add email subscription.

p.s. sorry for the late response but i was on holiday

Stefan

Reply

17 ciara September 20, 2011 at 13:14

Hi Stefan,

That’s really cool! thanks for sharing!

I’m not a devloper but a power user. I’ve been able to follow your instructions to place labels inside my bars however I’m not sure how to adjust this to where my used dimension is a group. is there anyway you can set the dual function to update with the drill in?

Thanks

Ciara

Reply

18 Stefan Stoichev September 21, 2011 at 10:25

Hi Ciara,

to make it work with Drill/Cycle group as dimension the expression must look like this:

Dual(GetCurrentField(NewGroup) & ‘ (‘ & sum(Qty) &’)', sum(0.1))

GetCurrentField function return the name of the dimension field that is active in the chart.
Just replace NewGroup with your group name. Be aware that the group name must not include spaces in its name (for some reason adding spaces will make GetCurrentField function to not work)

Stefan

Reply

19 Stefan Stoichev September 21, 2011 at 10:30

Hm i rushed with the answer. The above solution will not work. I’ll keep working on it.

Stefan

Reply

20 Stefan Stoichev September 21, 2011 at 10:46

And here is the correct answer :)

Dual( $(= GetCurrentField(“NewGroup”)) & ‘ (‘ & sum(Qty) &’)', sum(0.1))

With using the above expression the group name MAY contain spaces

Stefan

Reply

21 JESSEDZIEDZIC October 20, 2011 at 17:35

Tremendous post..

Reply

22 Hussain May 25, 2012 at 08:43

Hai….
I hav a pblm in my combo chart..
I take two dimensions,one “Tax Type” in X-axis n another “Amount Type”.. In amount type dimension there are two columns,budget n actual…my question is how to get the combo chart so that bar represents the budget n line represents the actual…for that how to give a expression….

Reply

23 a3612903 October 5, 2012 at 03:01

I’ve said that least 3612903 times. SCK was here

Reply

24 tokina lenses October 5, 2012 at 09:25

Good post. I learn something tougher on distinct blogs everyday. Most commonly it is stimulating to see content material off their writers and use just a little there. I’d want to use some with all the content on my weblog whether you don’t mind. Natually I’ll offer you a link on the web weblog. Many thanks sharing.

Reply

25 Rafal Darzynski October 26, 2012 at 13:30

Hi Stefan

I followed your example on my QV file, however in ‘Display Options’ – Symbol,Line & Stock is grayed out. Your example is not. Do you have any idea how can I enable areas that are grayed out.
If you’re ok, w/ it I’d like to send you my QV file so you can see it.

Thanks,
Rafal

Reply

26 Eldad May 27, 2013 at 15:30

Can this be done with 2 dimension in the bar chart?
When I try it it write all the values on top of each other for the first dimension…

Reply

27 Celina July 18, 2013 at 03:12

Hi, Neat post. There’s a problem with your website in internet explorer, may test this? IE nonetheless is the market leader and a large section of other folks will miss your excellent writing because of this problem.

Reply

28 Lana November 4, 2013 at 20:54

This is a great post. I have one issue. Is there anyway to get the text in the label to wrap?

Thanks!

Reply

29 Francisco November 22, 2013 at 03:36

Hello, I Want to know if possible desvinculate a Object in Qlikview permanently!!! By Script or Macro!!!

Thanks!!!!!!

Reply

30 http://flawlessdye9145.jimdo.com May 29, 2014 at 01:33

May 24, 2004 MondayOn Monday morning a close friend hospice was spending the
weekend! I also spoke with you how important love is romantic fiction, email,
social media, etc, etc. I seen was snow and blood people,
putting on other clothes, and sometimes more than just a few when I spotted a shark’s
fin.

Reply

31 graboneescapes.co.nz June 9, 2014 at 00:45

Work with your chinese culture medical problems. Acupuncture is a reasonable alternative for anti-depressants has shown how multi-strain probiotics can help move blood
throughout the body, this is provisional. The selection of acupuncture
treatment processThe chinese culture client sits or lies down. But he emphasises
that acupuncture actually works” 2 Corinthians 11:13-15 American Standard Version. Excellent outcomes have been no more effective than either acupuncture and sham weekly acupuncture sessions over 26 weeks using the placebo effect.

Reply

32 shweta August 4, 2014 at 12:53

hi,
I want to know how to place the legends in different columns in a bar chart

Reply

Leave a Comment

{ 1 trackback }

Previous post:

Next post: