topic Re: labels for subtotals? in QlikView

labels for subtotals?

Sat, 02 Nov 2013 03:57:09 GMT

I am new to Qlikview and I am hoping this is an easy thing.

I am creating a pivot table with sub totals based on the year dimension.

What I would like to show is the year that is being aggregated to show up as the label for the subtotal combined with a text.

So when I show the total for year 2003, I would like for the label to show "2003 Total" and the 2004 to show "2004 Total".

Is that possible?

Thanks!

Re: labels for subtotals?

Sat, 02 Nov 2013 07:13:22 GMT

I hope you want like this am i right

Re: labels for subtotals?

Sat, 02 Nov 2013 07:30:52 GMT

Right click then Properties

then goto Presentation tab

pick field for totals and underneath there is label textbox where

you can write expression for your label something like below where Year is your field

=YEAR & ' Total'

Re: labels for subtotals?

Anonymous — Sat, 02 Nov 2013 10:32:23 GMT

Ebolluca

Yesterday I was I was putting some labels onto some subtotals in a Pivot Table and did not consider I could use use an expression referencing field values as opposed to just typing test in.

Many thanks for pointing this simple yet effective labelling method.

Best Regards, Bill

Re: labels for subtotals?

Sat, 02 Nov 2013 11:32:29 GMT

Ebolluca,

Thanks!

However when I try to reference a field not even doing the concatenation it just puts a dash. When I leave it blank the word Total appears on each line.

Is there a special expression I need to write in order to get it to identify the Year that it is subtotaling?

Re: labels for subtotals?

Sat, 02 Nov 2013 11:34:37 GMT

Nirmal,

What I am trying to do is to get a subtotal line like you would in excel where it shows your rows and then there is one additional row that shows the subtotal for the set of rows above it.

I have the subtotaling working correctly but I can't seem to get the label to spit out the field that is being aggregated.

Thanks!

Re: labels for subtotals?

a_mullick — Tue, 05 Nov 2013 17:21:56 GMT

Hi Bill, Ebolluca

Did you get this to work? I tried this a while ago and like Kamau below did not get it to work. My feeling is that as the 'Label for Totals' field is not an expression which is evaluated over a dimension, it cannot resolve something like :

=YEAR & ' Total'

But please let me know if I am misunderstanding something.

Thanks,

Azam

Re: labels for subtotals?

Tue, 05 Nov 2013 18:07:52 GMT

Yes it works in totals, pivots and in dimension labels

Re: labels for subtotals?

Anonymous — Tue, 05 Nov 2013 18:17:51 GMT

Azam / Ebolluca

It works fine for me.

Could you post your qvw so I can look to see what is adrift ?

Best Regards, Bill

Re: labels for subtotals?

Gysbert_Wassenaar — Tue, 05 Nov 2013 18:26:32 GMT

No, all subtotal labels for one dimension will have the same value. =Year & 'Total' will only work when only one year is selected/possible. See this post for an alternative: Re: Breakdown of Total instead of just the total

Re: Re: labels for subtotals?

a_mullick — Tue, 05 Nov 2013 18:27:18 GMT

Thanks for looking Bill.

Attached is a simple example with a label defined for the sub-total over finMonth:

Azam

Re: labels for subtotals?

Tue, 05 Nov 2013 19:42:52 GMT

Hi,

If it is a variable (ex: vYear) you can reference in the expression and it will work even nothing is selected.

Gysbert is right all subtotals for one dimension have same value.

Re: labels for subtotals?

a_mullick — Wed, 06 Nov 2013 09:28:20 GMT

Hi Ebolluca,

Can you be more specific please? What goes in the variable, and how is that variable used in the 'Label for Totals' field?

Thanks,

Azam

Re: labels for subtotals?

Wed, 06 Nov 2013 17:36:19 GMT

Hi Azam,

Since labels for totals and dimensions will have identical values you can either type variable value as static or calculate it in script.

Here is a example from Script:

Let vYearTotalLabel = 'Y' & right(Year(Today()), 2)& '-Y' & right(Year(Today())+4, 2);
..

This will generate variable = Y2013 - Y2017

In the label of subtotal place following

=vYearTotalLabel

or anything like

=vYearTotalLabel & ' Total'

Best,

Ersen

Re: labels for subtotals?

Fri, 18 Jul 2014 20:08:25 GMT

I have a similar situation

I want each subtotal to have data of the group they are inserted in.

The thing is: when I have a selection, the subtotal is properly labeled with the expression ="Total " & [Group Name]

But when I remove the selection, then all my subtotal labels are set to "Total". I imagine this to be due to the fact that in the deselected state, there are multiple values of [Group Name], while in the selected state, there is one value for [Group Name] (ICMS, in the case below)

How can I print the [Group Name] in every subtotal label?

Below are my printscreen exemples (without the expression column)

Thanks

Re: labels for subtotals?

a_mullick — Mon, 21 Jul 2014 13:35:29 GMT

Hi,

If you haven't had a look look already, then as Gysbert says, Breakdown of Total instead of just the total, gives a good work-around for most cases.

Thanks,

Azam