Sub Accounts

[vinicius_siquei]

Folks - Please your help.

What's the best practice when I have to work with sub accounts.

For instance:

When the account '3.0.1' actually means the sum of all the accounts below whats your recommendation ?

I know that I could use left(account_num,5) = '3.0.1' - But there's any best method to do this ?

Regards,

Vinicius Siqueira

[whiteline]

Hi.

It depends on what you want to achieve. The term 'to work with' is too broad to talk about the best practice.

If the sum and hierarchy for pivoting is the only intention you could create two fields left(account_num,1)  and left(account_num,3) in addition considering them as account and sub account group.

There are differet tasks and different ways to handle hierarchy structures.

[vinicius_siquei]

Thanks for your answer.

Yes, my intention is for instance,  to sum from account 3.00.01 to 3.00.06.

What I would like to have is some intelligent way to go through these accounts.

Your suggestion to create levels of account is very interesting.

There's any other way?

[tresesco · Accepted solution]

May be you can try something like :

Subfield(Account_num, '.' , 1) as ParentAcc

Subfield(Account_num, '.' , 2) as Child1

Subfield(Account_num, '.' , 3) as Child2

[Anonymous]

I agree that group hierarchy is the way to go.

so you could name each section of your account code differently and split them in the script.

LOAD INLINE *[

Group     , SubGroup,LowestGroup

3          ,00               ,01    

3          .00               .06

];

and so on. 

[whiteline]

Hi.

Another example is a bit more complex. You create different dimentions for each level but also add records for each node (account and top subaccount) with empty values for childs. Suppose that each string has a unique id. It allows you to connect the hierarchy with facts that have different level of aggregation. For example planning facts and sales facts. And show them together in one pivot for comparison.

[vinicius_siquei]

Very Good!