7 Replies Latest reply: Jun 21, 2011 1:14 PM by Kaushik Solanki RSS

    Sorting Group into Subgroups



      I would like to take my Expense Accounts and put them into sub groups. So for my example:


      I have a field called [Hyperion Account] that lists out all of the groups expenses.  Within that I need to split the expense accounts into two groups; [Variable Expenses] and [Non Variable Expenses].  What would be the easiest and most effective way to go about doing this?


      Then, once I have the expenses split into [Variable Expense] & [Non Variable Expense].  I would like to create further sub groups such as:

           [Wages & Salaries]: consisting of


                (payroll taxes)



      Thanks for all your help,


        • Sorting Group into Subgroups
          Nagaian Krishnamoorthy

          For any [Hyperion Account], do you have details of group/subgroup to which it belongs?

            • Sorting Group into Subgroups

              The load statements is taking the data from an excel file, so the Hyperion Account field is not currently linked to any other fields if that's what your asking.


              OR you might be asking this?


              I have an expense account called 'Market Research', right now just a string of text under [Hyperion Account].  BUT I need to be able to sub group it  into [Hyperion Account], then [Variable], then [Advertising & Promotion], then '[Market Research].  The final product would be a pivot table or drilldown table I can expand each one of those catogories and get a total down to that detail level, or have it only half expanded to [Variable] for example, with a total of all the variable expenses.  Hope that helps. 


              Thank you

                • Sorting Group into Subgroups
                  Kaushik Solanki



                       All you have to do in load script is use of if statement and wildmatch.


                       For example


                       load [Hyperion Account],

                        if(wildmatch([Hyperion Account],'*ABC*'),'[Variable Expenses]','[Non Variable Expenses]') as SubGroup1



                       Here i have used only 1 if, but you can use nested if. If required.


                       Hope this is what you are looking for.



                  Kaushik Solanki

                    • Sorting Group into Subgroups

                      Can you please explain where in that statement I am extracting the expression (ex: salaries) and then labeling the sub group which I AM CREATING with this statement as [Variable Expense]?  Would SubGroup1 represent the newly created group?  And if that is the case, can I simply list all of the expressions in the [Hyperion Account] that I want in the group [Variable Expense] with this one statement?

                        • Re: Sorting Group into Subgroups
                          Kaushik Solanki



                             Can you please explain in more details with an example.


                             Also provide the data.



                          Kaushik Solanki

                            • Sorting Group into Subgroups

                              Unfortunetly I cannot provide the data because it involves confidential revenue and expense data for a publicly traded company.  Let me try and explain as clearly as I can.


                              I currently have an excel column labeled [Hyperion Account] which represent various expense accounts loaded into qlikview.


                              I need to break the loaded data down into the following sub groups, in this order.


                              so for example the 'Salaries' text string with subsequent # values would be broken down into


                              1. [Hyperion Account]

                              2. [Non-Variable]

                              3. [Wages & Salaries]

                              4. Salaries


                              also under [Wages & Salaries[ are the accounts text expressions already loaded in;

                                   'Payroll Taxes', 'Bonus', 'Benefits', Temporaries'