Accounts aggregation issue

[k_hatzikos50]

Hello everyone,

I have the following Account hierarchy (simplified):

+ Gross Profit

- Net Sales (INC)

- COGS (EXP)

- COGS per Kgr (NFIN)

The issue is that SAC does not calculate the parent member Gross Profit when COGS per Kgr is below Gross Profit unless I move it under a different parent but then I can't display the info according to requirements, which is COGS per Kgr just below COGS on the same table.

Any ideas?

Thank you!

P.S This is my first post 🙂

[DebjitSingha]

welcome to community 🙂
Can you share screenshot of your account dimension setup.

Thanks,

Debjit

[William_Yu1]

Hi k_hatzikos50，

Have you simply tried creae an account formula on ‘Gross profit' to just sum Net sales and COGS?

Best regards, William

[DebjitSingha · Accepted solution]

Hello k_hatzikos50.

There are multiple ways to get around this restriction of "aggregation on formula node".

One way would to use formula at parent level, instead of relying on "Account Type" and letting SAC decide how it will treat individual node for aggregation. This process will give you mode control as well. Here is an example-

In below example "Test Gross + Ind Exp" is simile addition of top nodes. Which is working as expected. Tough as I added it under "Operating Income", aggregating is not working. This due to the inclusion of formal that do not support aggregation.

Now what we can do is change Operating Income node to formula node.

Before:

After:

Formula at parent node now working:

Another simple fix would be to update currency and unit column: (PL650 is calculated node)

Before:

After:

Now your report will consider this node based on account type selected. In your case Gross Profit will consider COGS per Kgr (NFIN) as well.

Hope this helps. Let us know if you have any query around this topic.

Viewers/ readers - If you find above information helpful, feel free to up-vote (arrows on left side).

Thanks,

Debjit

[k_hatzikos50]

No, I don't want to add or subtract "Gross per Kgr" with "Net Sales" and "COGS" as it would make no sense, I just want to ignore it. But I need it there, in that hierarchy tree below "Gross Profit".

[DebjitSingha]

Got it. Formula parent node then.

Thanks,

Debjit

Note: comments can be used for reply. Keeps the thread clean for others.
🙂

[k_hatzikos50]

Still doesn't work, it sums up all three of them when set to INC.

[DebjitSingha]

Are you saying if your setup is below -

- Net Sales (INC)

- COGS (EXP)

- COGS per Kgr (INC)

Gross Profit comes out as = Net Sales + COGS + COGS per Kgr

Instead of = Net Sales - COGS + COGS per Kgr

even if COGS is defined as EXP type?

[k_hatzikos50]

Hi Debjit, when Units & Currencies is set to Currency it substracts the "COGS per kgr" from "Gross Profit".

[DebjitSingha]

Great now at least parent node is considering this node. Change account type to INC from NFIN

Thanks,

Debjit

[k_hatzikos50]

Hi Debjit,

Thank you so much for your detailed comment, I've experimented with Units & Currencies as you suggested and also with Aggregation Types and Exceptions but nothing seems to work.

I think I'll go with the formulas, there are about 15 such cases in my Account dimension and somebody will have to update those formulas in case there are changes but I don't see any other solution for now.

Thanks again!

Konstantinos

[DebjitSingha]

Hello Konstantinos,

goal is to setup calculated node with same property as that of parent node (except aggregation). For example- if parent node have currency defined then formula node should have same property.

Example below Operating Expense do not have any formula, instead a formula node as child (with similar properties). Aggregation works-

It should work regardless. If you want we can take a look, please share screenshot of the account dimension setup.

Thanks,

Debjit

[k_hatzikos50]

Hi Debjit,

Please see screenshot below:

Thank you very much,

Konstantinos

[DebjitSingha]

Hi Konstantinos,

change "Unit & Currency" entry to currency.
Thanks,

Debjit