6 Replies Latest reply: Nov 18, 2015 3:28 PM by Jonathan Poole

Get Totals in between for a group and also a grand total of a single dimension for all the expressions either in straight table or Pivot table.

Hi All,

I have created a straight table with only one dimension and respective 5 expressions in it. I want to get a total in between of the table for all the expressions and also a grand total to the end. For example if I have 20 fields in my dimension out of which 10 belong to technology, 5 belong to operations and 5 belong to others. So I want after the first 10 Technology items a field as "Total technology" and its sum across all the 5 expressions in the same way for Operations and Others and a grand total of all the items. I am familiar about the options showing complete total at the end or Top of table but I need the sums of each group and than the grand total.Even if its possible in Pivot table please help me in how it can be done.

Hemanth

• Re: Get Totals in between for a group and also a grand total of a single dimension for all the expressions either in straight table or Pivot table.

a pivot table with 'show partial sums' as well as a 6th expression that includes the total of all other measures would work

• Re: Get Totals in between for a group and also a grand total of a single dimension for all the expressions either in straight table or Pivot table.

Thanks for the reply but the requirement is to keep only one dimension standard. In the example you have given it should be like lets say after Africa and Asia there should be a field like "Total Africa Asia" and the totals in the same way after Europe and Middle East it should be "Total Europe Middle East". So in other words all these should come under a single dimension without partial sums between two dimensions.

• Re: Get Totals in between for a group and also a grand total of a single dimension for all the expressions either in straight table or Pivot table.

You can adjust the data model to create a custom dimension that includes various subtotals along the way. It would mean generating a new table in the data model based off the country / region which wouldn't be difficult. If you would like an example i can do that.

But... qlikview formatting is pretty flexible. Here is a formatted pivot table (just like before) with a bit more formatting

• Re: Get Totals in between for a group and also a grand total of a single dimension for all the expressions either in straight table or Pivot table.

Can you provide with the example for both of them please as I would like to know how it can be done in both ways.

Thanks

Hemanth

• Re: Get Totals in between for a group and also a grand total of a single dimension for all the expressions either in straight table or Pivot table.

Here is the screenshot from above . I'll make a change for the other way too in my next reply.

• Re: Get Totals in between for a group and also a grand total of a single dimension for all the expressions either in straight table or Pivot table.

Here is the other way

i used a straight table with the following load script to create a custom 'dimension' field that is sorted the way you want.  The script can be further cleaned up for easier manageability.

Region as Dimension,

Region,

if(Region='Middle East' or Region = 'Asia' or Region='Europe' or Region='Prev. Soviet Rep.',10,

if( WildMatch(upper(Region),'*AMERICA*'),20, if(Region='Africa',30,if(Region='Australia & Pacific',40))))  as SortOrder

Resident Countries;

//Associate Eurasia to relevant regions

'Subtotal: Eurasia'  as Dimension,

Region,

'11' as SortOrder

Resident Countries

where Region='Middle East' or Region = 'Asia' or Region='Europe' or Region='Prev. Soviet Rep.';

//Associate Americas to relevant regions

'Subtotal: AMERICAS'  as Dimension,

Region,

'21' as SortOrder

Resident Countries

where WildMatch(upper(Region),'*AMERICA*');

//Associate Americas to relevant regions

'Subtotal: AFRICA'  as Dimension,

Region,

'31' as SortOrder

Resident Countries

where Region='Africa';

//Associate Oceania to relevant regions

'Subtotal: Oceania'  as Dimension,

Region,

'41' as SortOrder

Resident Countries

where Region='Australia & Pacific';