About DouglasEaton

DouglasEaton

Hi, Is this what you are looking for: If so, my advice to you would be to change your invoice register to be timeless (I don't believe invoice details are meant to change over time; maybe corrected every now and then but not reviewed every month). Even so, the only thing you need, is to calculated a cumulative theoretical balance and a cumulative actual balance in order to figure out the adjustment or true-up amount that you need to post. When you have it, it's a matter of linking it to the current month based on your model time setting. Hope my example can help:

DouglasEaton

Hi, Not sure I understand what it is you are trying to achieve here. Is it somewhat similar to what I've mocked up down here? If so, are you trying to forecast your balance amounts in the "Expected Period for cash"?

DouglasEaton

Hi Tom, Here is one way to do it. Only limitation: you'd have to create additional loops for numbers higher then 999 billions I hope this helps. Regards

DouglasEaton · ‎03-12-2020

Actually I thought of a different solution. Less auditable than my first suggestion but would save up a little bit of size. Change your current line item and remove the switch-over boolean, add my formula with a reference to the switch-over period (see below) and make it current version only (formula scope). That way when you copy your version across none of the values will change. But you won't be able to see how actuals changed per version (if that was ever something you might have wanted). Formula: If item(time) < Version.Switch-over Period then Current Actual Module.Amount else Forecast Calculation

DouglasEaton · ‎03-12-2020

Hi Ryan, I believe the only solution in your predicament is to store the actuals per version. Because you effectively want to track changes in actual periods for different versions, hence the need to store those somewhere. Unfortunately that also means getting rid of the switch-over. This is what I have in mind: Pre requisite: 1) Store your actuals in a new module dimensioned by version (let's call it Actuals per Version) 2) Untick the switch-over boolean 3) Change formula scope to Current Version only, it's not mandatory but would make your model more efficient New Formula: If item(time) < Version.Switch-over Period then Actuals per Version.Amount else Forecast Calculation It's not perfect but it should solve your issue. Hope this helps Doug

DouglasEaton · ‎02-11-2020

Ok so I tested it and the problem comes from the formula in your "interest accretion" line item. The easiest way to solve it is to do all of your calculation against real time (any time periods will do, you just need to be able to use the PREVIOUS() function), map those to your fake time periods and bring the values back. s It should be fairly straight forward. Hope it solves it

DouglasEaton · ‎02-10-2020

if you give me the exact formula for ' Interest accretion at rate b/f - UPR' I can have another go at it. Did you try removing the formula and putting in my fix?

DouglasEaton · ‎02-10-2020

Hi Madhu, Without having a proper look at it it's a bit hard to tell. In theory it should work just fine. I wonder if it doesn't have something to do with your formula for "Interest accretion at rate b/f - UPR" as it also references the first two line items. Maybe try replacing that one by a input only and see if it solves the circular. If so we'll have to focus on that line item. Cheers

DouglasEaton · ‎02-07-2020

Thanks Amaya for the explanation. So I've managed to solve it using Time for sequential calculation. Warning, it aint pretty but it does work. The idea is simply to SUM then LOOKUP using one list member per parent. If you look at my print screens, the result is simply summing the values as per the last column. Then LOOKUP on it. The trick is to automatically get one list member per parent. Let me know if you need more explanation for each line item calculation. Cheers

DouglasEaton · ‎02-07-2020

I'm sorry but I still have no idea of what it is you're trying to achieve. If you attach some print screens I'd be more than happy to help.

DouglasEaton · ‎02-06-2020

Hi Chris, Can you explain a little bit more what you are doing, not sure I follow. If you could attach a couple of print screens of your lists/modules that would help a lot. Thx

DouglasEaton · ‎02-06-2020

Happy to help

DouglasEaton · ‎02-05-2020

Feel free to post here your design if you can't work it out. I'll take a look

DouglasEaton · ‎02-05-2020

Hi Takushimozuru, Yes you can. The easiest option is to use another list as your new Code format (Any Code list property in your example). Once you've got that you will need a mapping table between the list you used for your new codes and your Initial list (Test in your example). This mapping will be in the format of Test and applied to the new code list. The formula to used to create this mapping table will be Test.Test Item[firstnonblank:Test.Any Code] (Test.Test Item - this property needs to be created and is simply ITEM(Test)) Once you have created the mapping table, all you have to do is to reference it using a LOOKUP function to retrieve your Test list items in your module. It is also possible to do it with free text as your new code but it takes more space and requires additional calculations so I would advise against it if you can. If not here below is an example of how to do it. Note that the Flag I'm using in this example is simply a List named "Flag" with one member in it also named "Flag". Let me know if it's not clear. Cheers

DouglasEaton · ‎02-05-2020

Hi Madhu, I have another solution to offer: simply map your custom list (which ever is applied to your line items 1-3) to real time periods. All your have to do, is create the logic against real time (using SUM on a time period to allocate the line item value to a particular time period and LOOKUP that same time period to bring it back to your original module). See below print screens for details. Let me know if that works for you, Cheers

DouglasEaton · ‎11-01-2019

Hi, It would be super handy if the platform could highlight (underlined, bold,...) the logical path taken in a formula when drilling-down on a particular cell. Sometimes, with complex IF statements, it can be quite time-consuming to try to work out which branch of a formula the system is using. Highlighting it would save many people a considerable amount of time (developers as well as users). Regards, Douglas

DouglasEaton · ‎04-17-2019

Very interesting article, kudos

Online Status	Offline
Date Last Visited	7 hours ago