9 Replies Latest reply: Sep 14, 2009 11:32 AM by Martijn ter Schegget RSS

    Field Groups and Hierarchies

    vinces

      I am a new user using the Personal Edition of QV. I need help in creating an account hierarchy similar to those in OLAP tools such as Essbase or Analysis Services.

      My data file has account information in multiple Fields (columns). I want to group these by P&L categories such as GrossProfit, Sales and SGA for example. My goal is to start at the highest level such as NetIncome and drill down through the Account Groups and Accounts that make up the hierarchy.

      Any help would be appreciated, especially detailed step-by-step instructions to help out a novice! [:)]

       

      Thanks

        • Field Groups and Hierarchies
          John Witherspoon

          There are several ways to handle hierarchical information. You can do a hiearchy load, but I haven't actually had a use for it yet, only played with it to understand it. What I much more typically do is create cyclic or drill down groups, and leave my data completely unhierarchical.

          For example, I might have a table like this:

          Group Division Department Account Value
          Operations Rolling Roll Maint 12345 50000
          Operations Rolling Machine 1 12367 40000
          ...

          Even if Group, Division, Department and Account are a hierarchy, I'm likely to just read the table in flat like that.

          But I might want a bar chart where I can drill down through the hierarchy. So then I would define a drill down group made of of Group, Division, Department and Account. I would use that group as the dimension on a chart. I'd use sum(Value) as the expression for the chart. The chart would then start off displaying the total by Division for the Group. If I click on a Division, it would show me the total by Department for that Division. And so on.

          Or I could create a pivot table and put the four fields in as dimensions, sum(Value) is as an expresison, and ask for subtotals. I could then navigate through the hierarchy by expanding and collapsing nodes in my pivot table.

          So in general, my approach to hierarchies isn't to build them into the data model, but simply to define them on the fly in the charts as I need them. In general, I feel that's a more flexible approach, particularly when the hierarchies aren't rigid, and are just how the users want to see it right now.

          See attached example for how you could acutally implement everything I was saying above.

            • Field Groups and Hierarchies

              Hi,

              just one question when reading your post ...

               


              John Witherspoon wrote:
              See attached example for how you could acutally implement everything I was saying above. <div></div>


              A user using the "personal edition" is not able to open your example, isn't it?


              Best regards
              Stefan WALTHER

                • Field Groups and Hierarchies
                  John Witherspoon

                  I'm not really up to date on what Personal Edition can and can't open. If it can't open example files from other people, that sounds like a problem. Or maybe the problem is on my end - I'm mostly using 8.5, but I do have 9.0 personal edition on my machine so that I can get some experience with it before we upgrade officially. Maybe I saved it under personal edition, and therefore others can't open it? I'm saving in 8.5 and reposting just in case that fixes it.

                    • Field Groups and Hierarchies
                      vinces

                      Thanks for the posts but I can not open your files with my personal edition. I understand Groups as they pertain to dimensions in your first example. However, my data is not structured that way. Instead, I want to group Expressions together. Here is a simplified example of the file layout:

                      Date Customer Product Sales Rebates COGS Marketing Travel

                      2008.FEB WalMart Towels 12000 -200 -8000 -400 -100

                      2008.FEB WalMart Tires 19000 -500 -13000 -900 -500

                      I want to group similar accounts (they appear in the Expressions tab when I create charts) together such as Sales and Rebates grouped as NetSales, Marketing and Travel together as SG&A, NetSales and COGS as GrossProfit and GrossProfit and SG&A as NetIncome. . As this file shows, the group parent accounts do not exist. Having created groups, I wanted to then drill down into them starting at NetIncome all the way down to the the lowest account level details.

                       

                      Any further advice is appreciated.

                      • Field Groups and Hierarchies
                        Martijn ter Schegget

                        Hi John,

                        Personal Edition files can't be opened with other people's Personal Edition QVs. Luckily, a Partner License version can open pretty much anything Stick out tongue.

                        I took the liberty to copy the scripts and objects from your version into a new one; anyone should be able to open this one.

                        With regards,
                        Martijn

                  • Field Groups and Hierarchies
                    Martijn ter Schegget

                    Hi Vince,

                    As John already mentions: you could leave the different levels in your data as separate columns, and make a drill-down group in your user interface to use as a dimension in charts/graphs.

                    This assumes a balanced hierarchy: each path from the highest level (NetIncome) to the lowest level (Accounts) has the same number of steps. If you're using an unbalanced hierarchy (e.g. containing paths like 'Board of Trustees' -> 'Department of Education' -> 'DoE Staff' (3 steps) in one case, and 'Board of Trustees' -> 'Department of Computer Science' -> 'San Diego campus' -> 'DCS San Diego staff' (4 steps) in another case), QlikView has the 'hierarchy' and 'hierarchybelongsto' prefixes to the LOAD statement to transform your data.

                    Read pages 303 / 304 of the 'QlikView Reference Manual.pdf' in the QlikView installation directory (this is MUCH clearer than the QlikView help file) for an explanation.

                    Sorry that I can't offer a step-by-step, guess this depends too much on what you're trying to achieve.

                    With regards,
                    Martijn ter Schegget
                    CND Development

                    • Field Groups and Hierarchies
                      Martijn ter Schegget

                      Hi Vince,

                      I've played around with the example data in your last post (assuming the two rows you posted are your source data, and not the requested output format); see the attached QVW for the result. The issue is in trying to roll up the expressions (instead of dimensions); I've solved this by 'flattening' the data into a format with one row for each measure, with two columns: one showing which measure this is (Sales, Rebates, etc.) and the other showing the value. I've then built a hierarchy on the measures, and used this as a set of dimensions to group by in pivot tables.

                      By the way: this only works (i.e. is conceptually correct) if your measures are in the same unit (in this case some currency) and can be added together!

                      Hope this solves your problem.

                      With regards,
                      Martijn ter Schegget

                      PS: the data used when rolling up the expressions is remodelled to something like this:

                       


                      2008.FEB, WalMart, Tires, Sales, 19000
                      2008.FEB, WalMart, Tires, Rebates, -500
                      etc.


                       

                        • Field Groups and Hierarchies
                          vinces

                          Martijn,

                          You understood my request perfectly. Unfortunately, I can not open your QVW.

                          My question then is did you transform the data within QlikView or did you transform it prior to loading into QlikView?

                          Thanks for the prompt and thorough responses.

                            • Field Groups and Hierarchies
                              Martijn ter Schegget

                              Hi Vince,

                              I've used the following code to transform the data:

                               


                              LOAD
                              Date,
                              Customer,
                              Product,
                              pick(iterno(), 'Sales', 'Rebates', 'COGS', 'Marketing', 'Travel') as Measure,
                              pick(iterno(), Sales, Rebates, COGS, Marketing, Travel) as Value
                              RESIDENT SalesData
                              WHILE iterno() <= 5;


                              This code loops 5 times over each row in the original data (once for each of Sales, Rebates, COGS, Marketing, Travel), and for each loop loads one of the values 'Sales', 'Rebates', etc. as Measure and the matching field value as Value.
                              (The iterno() function returns 1 for the first loop over a row, 2 for the second loop, etc.; it starts back at 1 for the first loop over the next row, etc. The pick() function looks at it's first parameter, and returns the first of the other parameters ('Sales') if it's 1, the second ('Rebates') if it's 2, etc.).

                              With regards,

                              Martijn

                              PS: It's a QV9 file; are you using 9 or 8.5 to open it?