
Subtotals in dimensions
Jason Michaelides Apr 25, 2012 5:19 AM (in response to qlikfinance1)You'll normally get better help if you post your app with sample data if yours is sensitive.

Subtotals in dimensions
qlikfinance1 Apr 25, 2012 5:29 AM (in response to qlikfinance1)Thanks Jason.
e.g) GROUP:
 Sales $100
 Cost of sales $50
 Gross Profit $50 ($100  $50) (this is the subtotal I want to calculate)
 Variable Costs $10
 Operating Profit $40 ($50  $10) (this is the subtotal I want to calculate)
 Fixed Costs $20
 Net Profit $20 ($40  $20) (this is the subtotal I want to calculate)

Subtotals in dimensions
Jason Michaelides Apr 25, 2012 5:34 AM (in response to qlikfinance1)I assume there is some kind of category/client/cost centre dimension as well?

Subtotals in dimensions
Jagan Nalla Apr 25, 2012 5:39 AM (in response to qlikfinance1)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.

Subtotals in dimensions
qlikfinance1 Apr 25, 2012 5:51 AM (in response to Jagan Nalla)Jagan, the pivot idea only shows a 1 total at the bottom  not subtotals in between the data (e.g. after COS I'd like to see Gross Profit calculated as a subtotal $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!


Subtotals in dimensions
Masha Aleinikova Apr 25, 2012 5:42 AM (in response to qlikfinance1)You need to create your subtotal 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.


Subtotals in dimensions
Kuki K Apr 25, 2012 7:23 AM (in response to qlikfinance1)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: Subtotals in dimensions
qlikfinance1 Jun 25, 2012 6:06 AM (in response to qlikfinance1)Thanks for the assistance.
I found a great solution using dimensionality & a dummy dimension:
http://dvtrenches.wordpress.com/2011/05/11/qlikviewsubtotalingcustomizationpart2/