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