05-24-2022 02:22 PM - last edited on 07-31-2023 08:30 AM by JackLacava
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
05-24-2022 04:30 PM - edited 05-24-2022 04:30 PM
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 🙂
05-24-2022 04:36 PM
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.
05-25-2022 02:16 AM
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?
05-31-2022 03:17 PM
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.
05-25-2022 04:46 AM
Are you netting Intra-Profit/Cost Center within the Entity?
05-31-2022 03:16 PM
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.