Question Re: Accounts aggregation issue in Technology Q&A

Accounts aggregation issue

k_hatzikos50 — Thu, 18 Mar 2021 14:27:37 GMT

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 🙂

Re: Accounts aggregation issue

DebjitSingha — Thu, 18 Mar 2021 14:42:32 GMT

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

Thanks,

Debjit

Re: Accounts aggregation issue

William_Yu1 — Thu, 18 Mar 2021 15:03:56 GMT

Hi k_hatzikos50，

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

Best regards, William

Re: Accounts aggregation issue

DebjitSingha — Thu, 18 Mar 2021 15:33:18 GMT

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

Re: Accounts aggregation issue

k_hatzikos50 — Thu, 18 Mar 2021 16:07:34 GMT

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

Re: Accounts aggregation issue

DebjitSingha — Thu, 18 Mar 2021 16:19:04 GMT

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

Re: Accounts aggregation issue

k_hatzikos50 — Thu, 18 Mar 2021 16:29:01 GMT

Hi Debjit,

Please see screenshot below:

Thank you very much,

Konstantinos

Re: Accounts aggregation issue

DebjitSingha — Thu, 18 Mar 2021 16:36:53 GMT

Hi Konstantinos,

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

Debjit

Re: Accounts aggregation issue

k_hatzikos50 — Thu, 18 Mar 2021 16:58:34 GMT

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

Re: Accounts aggregation issue

DebjitSingha — Thu, 18 Mar 2021 17:09:37 GMT

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

Thanks,

Debjit

Re: Accounts aggregation issue

k_hatzikos50 — Thu, 18 Mar 2021 17:26:05 GMT

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

Re: Accounts aggregation issue

DebjitSingha — Thu, 18 Mar 2021 17:32:58 GMT

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?

Re: Accounts aggregation issue

k_hatzikos50 — Thu, 18 Mar 2021 17:38:47 GMT

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".

Re: Accounts aggregation issue

DebjitSingha — Thu, 18 Mar 2021 17:42:31 GMT

Got it. Formula parent node then.

Thanks,

Debjit

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