Pos / Neg check of total amount but keep all dimensions for future mapping

KennethNL
New Contributor II

Hi,

 

I have some accounts I either need to map to an asset account (100000) or liability account (200000).

The mapping should be based on the total account incl. sum of all dimensions.

e.g.

A#12345:I#IcpA = 2000

A#12345:I#ICPB = -3000

Then both accounts should be mapped to the liability account, but still to IcpA and IcpB.

 

If I create a derivative rule with create if < 0 and > 0 then it looks at all individual rows.

If I then create a prefix check account where I set all dimensions to e.g. Total_Member, then it will also be Total_Member it includes in the further mapping.

 

Any way I can do this in OneStream?

 

Thank you

 

/Kenneth

6 REPLIES 6

NicolasArgente
Valued Contributor

Hi Kenneth,
Some of us already covered this question on this post : https://community.onestreamsoftware.com/t5/Workflow-and-Data-Integration/Derivative-rules/td-p/7036
As you can see, there are also many ways to achieve that... but I am sure this post above will help you.
Please give a kudo if it helps 🙂

Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

Hi Nicolas,

 

Thank you, I looked at that post already, but it did not work.

If I add all dimensions and put them the same, then yes, I can get one positive or negative account.

But as mentioned, then I e.g. have IC_All instead of the individual IC partners (and other dimensions) which I still need to map to.

Maybe I am missing some steps?

Are you able to elaborate? I have tried several things already, unfortunately without luck.

Hi Kenneth,
Why do you include the IC in the Account mapping then? It is not the IC that defines the netting, right?
I also have a question regarding your point on the first post : if you use a derivative it will create a new line, but is that an issue for you?

Connect with me on:
LinkedIn: https://www.linkedin.com/in/nicolas-argente/
Website: https://aiqos.io
If you want to lift yourself up, lift up someone else.

Creating the extra row for derivative it not a problem.

But as mentioned below:

I might be able to skip ICP, so each ICP goes to different accounts.
But same question remains more or less, if I then have
A#12345:U1#ABC = -2000
A#12345:U1#DEF = 3000

Acc. 12345 maps to acc. A1234 or L1234 depending on the total amount.
UD1#ABC = U1#IT
UD1#DEF = U1#HR

But no matter what the department is, then it should look at the total of the account and base this for each row.
If I add a derivative rule saying A#12345=12345_PrefixCheck:U1#*=UD1_ALL
Then I will loose my UD1 details, right - but if I do not add the UD1_ALL then I will not get the right total of the account, right.

Dominic_A
Contributor

Are you netting Intra-Profit/Cost Center within the Entity?

KennethNL
New Contributor II

Yes, that is how it is currently being done. I might be able to skip this, so each ICP goes to different accounts.
But same question remains more or less, if I then have
A#12345:U1#ABC = -2000
A#12345:U1#DEF = 3000

Acc. 12345 maps to acc. A1234 or L1234 depending on the total amount.
UD1#ABC = U1#IT
UD1#DEF = U1#HR

But no matter what the department is, then it should look at the total of the account and base this for each row.
If I add a derivative rule saying A#12345=12345_PrefixCheck:U1#*=UD1_ALL
Then I will loose my UD1 details, right - but if I do not add the UD1_ALL then I will not get the right total of the account, right.