Sub-totals in dimensions

[#1]

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

[Jason_Michaelides]

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

[#3]

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)

[Jason_Michaelides]

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

[jagannalla]

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.

[masha-ecraft]

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:
load * inline [
Group, Amount
Sales, 100
Cost of Sales, 50
];

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

concatenate(OriginalData)
Load * Resident AdditionalData;

drop table AdditionalData;

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.

[#7]

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.

Your assistance is much appreciated!

[#8]

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......

[#9]

Thanks for the assistance.

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

http://dvtrenches.wordpress.com/2011/05/11/qlikview-subtotaling-customization-part-2/