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.

[hbandari3636]

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.

Thanks in advance.

Hemanth

[JonnyPoole]

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

[hbandari3636]

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.

[JonnyPoole]

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

[hbandari3636]

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

[JonnyPoole · Accepted solution]

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

[JonnyPoole]

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.

//load granular dimension values

load

  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

load

  '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

load

  'Subtotal: AMERICAS'  as Dimension,

  Region,

  '21' as SortOrder

Resident Countries

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

//Associate Americas to relevant regions

load

  'Subtotal: AFRICA'  as Dimension,

  Region,

  '31' as SortOrder

Resident Countries

where Region='Africa';

//Associate Oceania to relevant regions

load

  'Subtotal: Oceania'  as Dimension,

  Region,

  '41' as SortOrder

Resident Countries

where Region='Australia & Pacific';

//load Grand total line

load

  'Grand total' as Dimension,

  Region,

  '51' as SortOrder

Resident Countries;