8 Replies Latest reply: Jun 25, 2012 6:06 AM by qlikfinance1

# Sub-totals in dimensions

Hi!

I want to use QV for financial info - sales, cost of sales, variable costs, & fixed costs.  These categories are listed in my dimension field called 'Group'.  Is it possible to have sub-totals in this list of dimensions?

e.g) GROUP:

- Sales

- Cost of sales

- Gross Profit  (this is the sub-total I want to calculate)

- Variable Costs

- Operating Profit  (this is the sub-total I want to calculate)

- Fixed Costs

- Net Profit  (this is the sub-total I want to calculate)

Thank you

• ###### Sub-totals in dimensions

You'll normally get better help if you post your app with sample data if yours is sensitive.

• ###### Sub-totals in dimensions

Thanks Jason.

e.g) GROUP:

- Sales \$100

- Cost of sales \$50

- Gross Profit  \$50 (\$100 - \$50)  (this is the sub-total I want to calculate)

- Variable Costs \$10

- Operating Profit \$40 (\$50 - \$10)  (this is the sub-total I want to calculate)

- Fixed Costs \$20

- Net Profit  \$20 (\$40 - \$20)  (this is the sub-total I want to calculate)

• ###### Sub-totals in dimensions

I assume there is some kind of category/client/cost centre dimension as well?

• ###### Sub-totals in dimensions

Take a pivot table and add dimension as Group in expression write your expressions. Now go to presentation tab there select your dimension and check "Show Partial Sums".

Hope it helps you. If not please provide sample file it helps others to solve the issue.

• ###### Sub-totals in dimensions

Jagan, the pivot idea only shows a 1 total at the bottom - not sub-totals in between the data (e.g. after COS I'd like to see Gross Profit calculated as a sub-total \$100 less \$50 cost of sales.  Only the 4 fields Sales/COS/VC/FC are in the column titled 'Group', which is my dimension.

 Income Statement R Sales 100 COS 50 VC 10 FC 20 TOTAL 20

Jason, the other fields are product type, date, then actual & budget.

• ###### Sub-totals in dimensions

You need to create your sub-total categories in the Group field and link them to your data in the sames way as the other categories are linked.

This is an example:

OriginalData:
Group, Amount
Sales, 100
Cost of Sales, 50
];

'Gross Profit' as Group,
Amount,
if(Group='Sales',1,-1) as Coefficient
Resident OriginalData
where Group = 'Sales' or Group = 'Cost of Sales'
;

concatenate(OriginalData)

Chart dimension should be Group and the formula for the amount like that:

sum(Amount * if(isnull(Coefficient),1,Coefficient))

Additional groups can also be defined and linked to the existing groups in Excel.

• ###### Sub-totals in dimensions

Hi .....u can do the following:

Take a straight chart

and in dimension field ->  sum(total Sales)

sum(total [Cost of Sales])

sum( total Sales -[cost of sales])

and in expression u take =1

tatz it........

similarly proceed wit other values in dimension

i hope it workz......

• ###### Re: Sub-totals in dimensions

Thanks for the assistance.

I found a great solution using dimensionality & a dummy dimension: