Captions on charts and tables are quite useful in QlikView, the colouring of the caption can denote currently selected objects and the caption provides an anchor to pick up and move objects by. If you are going to have captions then you should at least use them well. How? Read on..…
When you first create your chart, setting only the bare essentials up first, your caption may look a bit like this one here.
The actual coding behind the expression is shown in the header (which is meaningless to anyone who is not a developer) and there is some chart junk in the form of maximize and minimize icons that you probably don’t need.
First of all, you should always provide a label for any expressions, whether that label is visible or not, that is meaningful to the user. Any icons that are not required can be turned off. The XL icon is a matter of taste – I tend to leave it on.
But why stop there? You have space taken up by a caption, so why not use it? In the caption you can type plain text, or (more usefully) you can provide an expression. Expressions always start with an equals sign. If you are showing numbers then ensure that you use the num function to format them correctly. In this example total sales are shown.
Your chart or table will no doubt have a dimension on it, so you can also use the space to give details of what the dimension is that the chart is over. If your chart or table uses a Cycle group then you can use the GetCurrentField function to retrieve the current value.
If you still have some room on your caption bar you could provide even more information here. It may be useful to show the date range of the data that you are displaying or, as I have here, a count of the rows of data that make up the values in the chart. You can do all kinds of expressions in a caption so give some thought as to what is going to be most useful to your users.
Here is a completed chart. The caption is showing additional data in the form of the total sales (which you would have to add up otherwise) and the number of sales – which is not represented at all. If the dimension wasn’t a Cycle group then you could do away with the dimension legend on the chart and only have it in the caption. In the image above the caption text is restating the dimension in the caption, so that all the headline information is in one place.
The code required for the caption bar in the chart above is as follows:
=’Sales by ‘ & GetCurrentField([SalesCycle]) &
‘ (£’ & num(sum([Sales Amount]), ‘#,##0’) & ‘ – ‘ &
num(sum(RowCount), ‘#,##0’) & ‘ Sales)’
As well as captions; expressions built up in this way can be shown in Text Objects. A ticker bar of information along the top of a page is a good way of getting lots of information in to a small amount of space.
So, what can you fit into the captions in your apps?
Thank you, Steve, for the post!
Just wonder if you know some short and sweet way to export to Excel along with their captions?
You could write to a text file with a line of preamble using a macro – you will be able to find code to do this on QlikCommunity. The better way would be to use a product called NPrinting – there is a price tag and a learning curve to this, but getting it will open up a lot of opportunities for improving your output.
We use nPrinting, and while it’s a great product for automating report creation and distribution, I don’t find it much help in formatting output. For example, if you are exporting to html, we have tried every known way to get the html vertically formatted so the output is anchored to the top of the cell, rather than the bottom, without success.
Thanks for your comment. When you use NPrinting to output to HTML you can use a template, so you should be able to apply a CSS style sheet to format the output correctly. Please get in touch with me directly if you would like me to look into this further for you.
Hi Steve,
using GetCurrentField to show the current field of a C.Group in caption is really interesting! Thx so much.
Regards
Peyman
Glad you like it! There are many many functions you can concatenate in to captions and Text Boxes. If you can picture what you want to achieve there is usually a way of coding it.
Hi Steve
I have a question.
1) I heard captions consume huge lot of memory. So what do you think of instead of captions, having text box and doing the same job ?
2) I need to enable Menu option for notes and annotations to be visible in my ipad. For this caption needs to be enabled, is that possible without enabling Caption, can i enable Menu ?
Caption consumes a space in Ipad. But without which Notes and annotations, i am not getting it.
So whats your suggestion on it ?
Hi Hari – yes, I am aware that expressions in captions can be expensive – but no more than the same expression in a Text Object or Chart. I think the benefits as described above outweigh the slight performance hit.
Regarding captions on iPad: it may be that you need them to get to the annotations (I’ve not tried without). That being the case just make sure you put something interesting in the caption!
Hi Steve —
My latest epiphany was to include the date range of the data (in this case, as the dimension label):
=Date(min({}Calendar.Date)) & ‘ – ‘ & Date(Max({} Calendar.Date))
That way when the user exports the table, they don’t have to remember what filters they’ve set.
Hi Mike, indeed, most of my Expression Labels have calculated labels. Date ranges are a good case in point.
Hi Steve,
thanks for this post, indeed I sometimes struggle to work out a useful caption for business users.
I have one question: is there a way to find out whether an object is minimized or not? Often our clients request several charts/tables on one sheet, so we create several charts and use autominimize option to display only one at a time. Since there’s much less space when an object is minimized, I’d like to define a “short” and “long” version of a caption. Thank you.
As far as I am aware there is no native way of finding this out. You can with macros – but I am not sure whether you can have a trigger fire to read the current state as each chart changes. This is why I tend to handle switching between charts with Show/Hide conditions fired by buttons. See this post and video for examples and instructions how to implement: QlikView Buttons.
hi steve i got lots of help from your blogs. i need one help i do lots of formula but cannot succeeded.
i want to count number of bars of a bar chart. please help me if you can.thanks and regards
Thanks for the comment on the blogs. Depending on the expression, you should be able to count distinct on the dimension, eg: COUNT(DISTINCT MyDimension). This will not however take into account the expression, but Set Analysis can help you do this. For example, if the expression on your chart is Sum(Sales), you could count the bars by doing COUNT({0′}>} DISTINCT MyDimension). Hope that helps!
Hi Steve,
I have an requirement where I have to show the no of rows/no of possible rows in caption based on selection of dimension and metric i.e. the table is getting created dynamically based on Dimension and Metric selection.
Could you please help me on this.?
Thanks in advance,
Punit
Punit, I always add a counter field in the load script, so I can have a caption like this:
=’Client List (‘ & num(sum(ClientCount), ‘#,##0’) & ‘ rows)’
Or if you don’t have a counter you can have:
=’Client List (‘ & num(count(DISTINCT ClientID), ‘#,##0’) & ‘ rows)’
If you can gaurentee only one selection will exist for dimension and metric you could have something like:
=Only(Metric) & ‘ by ‘ & Only(Dimension) & num(sum(aggr(if($(=Only(Metric)) <> 0, 1, 0), [$(=Only(Dimension))])), ‘#,##0’)
This should then give you a count of rows. You will need to look into the AGGR function to see why this should work. I’ve assumed the expression itself will be in the Metric field, but if you have separate values for the Metric name and Metric expression you will need to use this. I tend to employ variables for this sort of thing – as it allows you to break things up into manageable chunks.
Good luck!
[…] Quick Intelligence – QlikView Caption Contest […]