Subtotal Line Pivot

Fri, 03 Dec 2010 15:15:27 GMT

guys,

I am still learning QV but it seems like a great tool to use!!

my question is that I am in the process of doing a roll up hierarchy based on lines with total other income being the parent and a bunch of lines directly below that. I have this set up in a pivot where I am loading the data from two excel tabs which would represent what the customer data is. This includes a node ID and I also have a hierarchy table set up. script is below for your viewing pleasure [:)]

now when 'uploading' these entries there needs to be a subtotal that is not called 'total' but called total other income which should be a calculated dimension but I am unsure how to make that work correctly( remember its part of the hierachy ) Because eventually this total other income will roll up to another line called total revenue.

NodeID	ParentID	NodeName
1	-	Total Other Income
2	1	Trust Income
3	1	Service Charges
4	1	Total Mtg Banking
5	1	Brokerage Fees
6	1	Loan Fees
7	1	Merchant & Credit Card
8	1	Other Fee Income

Unfortunately I have the personal edition and I can't open others files. From the sounds of it in the coming months we will be acquiring this product but need to build some proof of concepts.

NodeName	Actual	Forecast	% Difference
Brokerage Fees	$55.20	$60.72	9%
Loan Fees	$8.86	$9.74	9%
Merchant & Credit Card	$0.18	$0.19	9%
Other Fee Income	$5,473.11	$6,020.42	9%
Service Charges	$44.98	$49.48	9%
Trust Income	$17,043.03	$18,747.33	9%
Total	$22,625.35	$24,887.88	9%


NodeName	Actual	Forecast	% Difference
Brokerage Fees	$55.20	$60.72	9%
Loan Fees	$8.86	$9.74	9%
Merchant & Credit Card	$0.18	$0.19	9%
Other Fee Income	$5,473.11	$6,020.42	9%
Service Charges	$44.98	$49.48	9%
Trust Income	$17,043.03	$18,747.33	9%
Total Other Income	$22,625.35	$24,887.88	9%

ODBC

CONNECT TO

[Excel Files;DBQ=C:\Documents and Settings\EFCDMWS\Desktop\QV stuff\plan\hierarchy sample structure.xlsx];

Hierarchy

(NodeID, ParentID, NodeName)

SQL

SELECT

NodeID,

ParentID,

NodeName

FROM

`C:\Documents and Settings\EFCDMWS\Desktop\QV stuff\plan\hierarchy sample structure.xlsx`.`Sheet1$`;

SQL

SELECT

FROM

`C:\Documents and Settings\EFCDMWS\Desktop\QV stuff\plan\roll up revenue sample.xlsx`.`testing$`;

SQL

SELECT

FROM

`C:\Documents and Settings\EFCDMWS\Desktop\QV stuff\plan\roll up revenue sample.xlsx`.`'testing 2$'`;

Subtotal Line Pivot

Tue, 07 Dec 2010 10:55:58 GMT

Could you be a bit more clear as to what you want in terms of output. Also would be easier if you could upload sample data

topic Subtotal Line Pivot in QlikView

Subtotal Line Pivot

Subtotal Line Pivot