1 Reply Latest reply: Dec 7, 2010 5:55 AM by umangu2000 RSS

    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 [:)]

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