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
CONNECTTO
[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$'`;
