topic Re: Sorting Group into Subgroups in QlikView

Sorting Group into Subgroups

Mon, 20 Jun 2011 16:10:03 GMT

Hi,

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

(salaries)

(payroll taxes)

etc.

Thanks for all your help,

Alex

Sorting Group into Subgroups

nagaiank — Mon, 20 Jun 2011 16:26:18 GMT

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

Sorting Group into Subgroups

Mon, 20 Jun 2011 16:51:22 GMT

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

kaushiknsolanki — Mon, 20 Jun 2011 19:28:46 GMT

Hi,

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

from.........

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

Hope this is what you are looking for.

Regards,

Kaushik Solanki

Sorting Group into Subgroups

Tue, 21 Jun 2011 16:25:02 GMT

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

kaushiknsolanki — Tue, 21 Jun 2011 16:54:24 GMT

Hi,

Can you please explain in more details with an example.

Also provide the data.

Regards,

Kaushik Solanki

Sorting Group into Subgroups

Tue, 21 Jun 2011 17:09:00 GMT

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'

thanks,

Alex

Re: Sorting Group into Subgroups

kaushiknsolanki — Tue, 21 Jun 2011 17:14:04 GMT

Hi,

I am really sorry i am unable to understand your requirement.

Is it possible for you to create a dummy data and send me.

Regards,

Kaushik Solanki