Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
SELECTNodeID,
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$'`;
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