topic Re: Use Dimension element as Total label ? in QlikView

Use Dimension element as Total label ?

Sat, 18 Jan 2014 17:05:59 GMT

Below Niveau3 elements sums up to 'Resultat' in Niveau4.

I have renamed Total label to blank, and right aligned Niveau4.

Is it possible to combine Total cell with Niveau3 'Resultat' like I have done below in excel?

Kind regards

Jerrik Walløe

Coop Denmark

Re: Use Dimension element as Total label ?

Gysbert_Wassenaar — Sat, 18 Jan 2014 17:33:46 GMT

No, that's not possible. The total label can only have one value for the entire dimension column. But you can create a new field in the script and use that as dimension. See here for an example: Custom Labels in Pivot Table

Re: Use Dimension element as Total label ?

Sat, 18 Jan 2014 18:17:01 GMT

I had a look at the linked solution. It may work in small scale, but my hierachy is big and not static.

I hope for other solutions. Total labels can be filled with Dimension element text, in others BI tools. So perhaps QV also.

Re: Use Dimension element as Total label ?

iktrayanov — Sat, 18 Jan 2014 18:28:45 GMT

You can use dimensionality() to redefine the Totals in your pivot table.

Re: Re: Use Dimension element as Total label ?

iktrayanov — Sat, 18 Jan 2014 18:46:31 GMT

I created an example. Take a look.

Re: Use Dimension element as Total label ?

Sat, 18 Jan 2014 19:16:58 GMT

Label for grandtotal is changed, but could the label for subtotal be changed?

For Dim2 I tried to set [Label for total] to [=Before(DIM1)] but then just '-' are displayed. Same for [=Only(Dim1)]

Re: Use Dimension element as Total label ?

iktrayanov — Sat, 18 Jan 2014 19:29:09 GMT

You can change the label for subtotals by using

If (dimensionality() = 1, .... Notice that the dimensionality for normal cell is =2 for subtotals is =1 and for grand total is =0. Using that you can redefine what is shown in any of the cells.

Re: Use Dimension element as Total label ?

Sat, 18 Jan 2014 20:06:31 GMT

=If (dimensionality() =1 , DIM1) dont work. Nor =If (dimensionality() =1 , only(DIM1))

Re: Use Dimension element as Total label ?

iktrayanov — Sat, 18 Jan 2014 22:43:36 GMT

Where are you using this in the total label or in the expression?

Can you explain in more detail what you are trying to do so I can try to help.

Re: Use Dimension element as Total label ?

iktrayanov — Sat, 18 Jan 2014 23:51:10 GMT

Is that what you want?

Re: Use Dimension element as Total label ?

Mon, 20 Jan 2014 07:00:49 GMT

Almost. In Image002.png, attached to my original post, you can see the total label renamed with the DIM1 element.

Re: Use Dimension element as Total label ?

iktrayanov — Mon, 20 Jan 2014 15:46:22 GMT

I am afraid that is the best I can do but with some changes in style and presentation you can get it to look like this

Re: Use Dimension element as Total label ?

iktrayanov — Mon, 20 Jan 2014 16:24:14 GMT

I think got it to do what you need

Go to the Style tab and check the indent mode like this

Re: Use Dimension element as Total label ?

Tue, 21 Jan 2014 06:37:09 GMT

Superb!

A standard QV solution