Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Highlighted
Not applicable

Subtotal Line Pivot

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 [Smiley Happy]

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.

NodeIDParentIDNodeName
1-Total Other Income
21Trust Income
31Service Charges
41Total Mtg Banking
51Brokerage Fees
61Loan Fees
71Merchant & Credit Card
81Other 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.

NodeNameActualForecast% Difference
Brokerage Fees$55.20$60.729%
Loan Fees$8.86$9.749%
Merchant & Credit Card$0.18$0.199%
Other Fee Income$5,473.11$6,020.429%
Service Charges$44.98$49.489%
Trust Income$17,043.03$18,747.339%
Total$22,625.35$24,887.889%
NodeNameActualForecast% Difference
Brokerage Fees$55.20$60.729%
Loan Fees$8.86$9.749%
Merchant & Credit Card$0.18$0.199%
Other Fee Income$5,473.11$6,020.429%
Service Charges$44.98$49.489%
Trust Income$17,043.03$18,747.339%
Total Other Income$22,625.35$24,887.889%








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





Tags (1)
1 Reply
Not applicable

Subtotal Line Pivot

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

Community Browser