Skip to main content
Newcomer
May 24, 2022 6:22 PM UTC

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

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

Veteran
May 24, 2022 8:30 PM UTC

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 🙂

KennethNLAuthor
Newcomer
May 24, 2022 8:36 PM UTC

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.

Veteran
May 25, 2022 6:16 AM UTC

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?

OneStream Employee
May 25, 2022 8:46 AM UTC

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

KennethNLAuthor
Newcomer
May 31, 2022 7:16 PM UTC

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.