Derivative rules

SC
New Contributor III

Hi experts,

 

I have a group of GL accounts that I need to map to either an expense / revenue based on the sum of the balances.

I thought this would be easy enough with a derivative rule; first create an interim rule to aggregate the accounts and then create a final rule to check if the balance is greater / less than 0.

Finally, I have used the final derivative rule in my target account mapping.

SC_0-1651213324248.png

But, this is not quite working as expected.

It seems to just add the negative balances of the individual GL accounts under the "_N" rule and the positive balances under "_P" rule, therefore both my expense and revenue account in OS ends with a balance.

Could you please suggest what I am doing wrong?

Regards,

S

 

 

1 ACCEPTED SOLUTION

Sai_Maganti
Contributor II

My guess is that, in order for the first line in your DR to create an aggregation you must use all the dimensions including any attribute dimensions similar to this:

E#[*]=All_Entities:F#[*]=All_Flow:IC#[*]=All_IC:UD1#[*]=All_UD1:UD2#[*]=All_UD2........:A1#[*]=All_A1:A2#[*]=All_A2......

This will then create a one line sum for all your specified accounts and assign that sum to A#[Map_SwapProd] and then the next 2 rules should work fine.

View solution in original post

7 REPLIES 7

NicolasArgente
Valued Contributor

Hi SC,

There are many ways to do that but to stick to your method can you try it like this :

You do
A#SwapProd = A#SwapProd_New and multiply by 1
Then after you add a rule a bit like you did above that says
A#SwapProd_New = A#SwapProd_P If positif
A#SwapProd_New = A#SwapProd_N If Negative
Can your try?
Please give a thumbs up if it helps.
Nic

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.

SC
New Contributor III

Thanks for your response Nic. So, you are saying I can't get this working through a >x  and <x operator, but I actually have to write a rule for this?

Regards,

S

NicolasArgente
Valued Contributor

You need one rule that adds them up. It is the first one with NEW in the name
Then according to the total of the NEW you will know the sign

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.

SC
New Contributor III

Hi Nic,

Based on what you suggested, this is what I tried:

 

SC_0-1651241154509.png

1. Aggregate the accounts into Map_SwapProd

2. Multiply by 1 and create Map_SwapProd_New

3. Check if Map_SwapProd_New >0 0r <0 and map to Map_SwapProd_Po / Map_SwapProd_Ne

 

In my transformation rule, I then have Map_SwapProd_Po mapped to an expense account and Map_SwapProd_Ne mapped to a revenue account. So, the total balance can either go to an expense or  a revenue account.

 

However, I still see data in both accounts and I can see that the GL accounts with negative balances are mapping to Map_SwapProd_Ne and the GL accounts with +ve balances mapping to Map_SwapProd_Po. This seems strange as I am creating both of them from Map_swapProd_New which should have the aggregate balance.

 

Regards,

S

Regards,

Sounak

 

Sai_Maganti
Contributor II

My guess is that, in order for the first line in your DR to create an aggregation you must use all the dimensions including any attribute dimensions similar to this:

E#[*]=All_Entities:F#[*]=All_Flow:IC#[*]=All_IC:UD1#[*]=All_UD1:UD2#[*]=All_UD2........:A1#[*]=All_A1:A2#[*]=All_A2......

This will then create a one line sum for all your specified accounts and assign that sum to A#[Map_SwapProd] and then the next 2 rules should work fine.

OS_Pizza
Contributor III

I am trying to aggregate all my rows via target derivative. I am unable to get the syntax that you have mentioned.Can you see if the below syntax is correct?

[V#[*]=All_View:E#[*]=All_Entities:A#[*]=All_Account:F#[*]=All_Flow:IC#[*]=All_IC:UD1#[*]=All_UD1:UD2#[*]=All_UD2:UD3#[*]=All_UD3:UD4#[*]=All_UD4:UD5#[*]=All_UD5:UD6#[*]=All_UD6:UD7#[*]=All_UD7:UD8#[*]=All_UD8]= Sum_all

AndreaF
Contributor III

The derivative rule would work as you expected if the Pov of the incoming data on all other dimensions was the same. each combination with different Ud1/Ud2/etc. members is considered as a different bucket.