15 Replies Latest reply: Nov 28, 2016 8:00 AM by Sunny Talwar

# How to add a second total row in straight table?

Hi,

I have a simple question and I can't seem to find the solution. I have a straight table with a single dimension and a few expressions.

I also added a column with the sum of the values of the three columns, and a total row at the bottom. The table looks something like this.

Dim     E1     E2     E3     Total

A         10     15       5      30

B          8       3       4       15

C          7       4      14      25

Total     25      22     23     70

I need to add another row underneath the total row with the relative value of the total of each expression. Something like this.

Dim     E1     E2     E3     Total

A         10     15       5      30

B          8       3       4       15

C          7       4      14      25

Total     25      22     23     70

%         36%   31%  33%  100%

Is this possible? Is there a simple way to do it?

Thank you very much

• ###### Re: How to add a second total row in straight table?

Look at the attached

• ###### Re: How to add a second total row in straight table?

Hi Sunny,

can you do IslandDim filed in front itself?

the above result can achieve in front end?

• ###### Re: How to add a second total row in straight table?

I like the simplicity of this solution. But what if the number of values of dim1 changes? One would have to add an extra value to island dim.

Is there a way to create island dim with the correct number of values automaticaly?

Thank you very much

• ###### Re: How to add a second total row in straight table?

The number of values in not dependent on number of values under Dim1. It depends on the number of totals you need. Here you needed Total and %, so, we had 2 + 1. If you need three total (Total, Avg and %) then you will need 3 + 1. But if Dim1 increases from 3 to 1million you will still need Dim to have values 1, 2, & 3.

• ###### Re: How to add a second total row in straight table?

I just realized it doesn't depend on that.

• ###### Re: How to add a second total row in straight table?

• ###### Re: How to add a second total row in straight table?

Not possible in sense.. There will be always 1 total row

• ###### Re: How to add a second total row in straight table?

Sorry, I meant qlikview.

• ###### Re: How to add a second total row in straight table?

You can write the expresions as

sum(E1)

sum(E2)

Sum(E3)

sum(E1+E2+E3)

sum(E1+E2+E3)/sum(All E1+E2+E3)

• ###### Re: How to add a second total row in straight table?

Did the sample I provided not what you want?

• ###### Re: How to add a second total row in straight table?

It is possible also in Qlik Sense - see attached.

cheers

Lech

• ###### Re: How to add a second total row in straight table?

This is not what you have done using Show total option. This involves data modification.

But Nice Workaround. Worth trying.

Workaround always works in qliksense.

• ###### Re: How to add a second total row in straight table?

Yes, It requires little bit of modification and is not using TOTAL functionality. However you get possibility of:

• coloring total row
• you can place it on top/bottom (left/right in pivots) which is not available in QlikSense
• it is not extension
• works probably better than fake dimension as it is using natural data reletionships to group data
• and at the end it is not hard to implement

regards

Lech

• ###### Re: How to add a second total row in straight table?

hi

You can try like this,

In straight table we can see the totals for each and every expression

and

by relative option we can see the percentages for that values.....

but in straight table we can only see  the totals in one row only.

• ###### Re: How to add a second total row in straight table?

Hi,

This is solution/workaround which can be used in both: QlikView & Sense. Lets just assume we want to create table where we want to look at sales by Customer and have Total \$ and Total %

First step is to create additional grouped dimension by applying script from below. It creates total for all available values under dimension which we want to use as grouping. Next step is simply to use grouped dimension field as your dimension WITHOUT Totals as totals will be created automaticly.

Obviously then you have to just take care of proper sorting which is fairly easy.

Attached is sample app with this result:

FIELDVALUE('%CustomerId',ITERNO()) AS Customer2

 AUTOGENERATE (1) WHILE NOT ISNULL(FIELDVALUE('%CustomerId',ITERNO()))

;

Concatenate (Customers)

 %CustomerId, 'Total' as Customer2 Resident Customers ;

Concatenate (Customers)