topic Sub-totals in dimensions in QlikView

Sub-totals in dimensions

Wed, 25 Apr 2012 08:31:46 GMT

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

Jason_Michaelides — Wed, 25 Apr 2012 09:19:46 GMT

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

Sub-totals in dimensions

Wed, 25 Apr 2012 09:29:43 GMT

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

Jason_Michaelides — Wed, 25 Apr 2012 09:34:03 GMT

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

Sub-totals in dimensions

jagannalla — Wed, 25 Apr 2012 09:39:36 GMT

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

masha-ecraft — Wed, 25 Apr 2012 09:42:49 GMT

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.

Sub-totals in dimensions

Wed, 25 Apr 2012 09:51:38 GMT

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!

Sub-totals in dimensions

Wed, 25 Apr 2012 11:23:39 GMT

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

Mon, 25 Jun 2012 10:06:27 GMT

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/