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
Thanks for all your help,
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.
All you have to do in load script is use of if statement and wildmatch.
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.
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?
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]
3. [Wages & Salaries]
also under [Wages & Salaries[ are the accounts text expressions already loaded in;
'Payroll Taxes', 'Bonus', 'Benefits', Temporaries'