Question on Formula

Hi,

I'm trying to create a Line Item Formula in where every 10th of the Month/ or next business day, Money comes out of another Line Item. Please advice.

Thank you

Tags

Modeling

Calculation Functions

Accepted answers

Hello,

There are 5 line items you will need to set up. I've attached screenshots below:

1) A line item that is populated with the current date. This will be the input to the logic described below

2) A line item that uses the DAY() formula which returns the day # of the month

3) A line item that uses the WEEKDAY() formula to return the number of the day of the week it is. (Monday = 1, Sunday = 7)

4) A line item which calculates days till next business day (if the 10th falls on a Saturday or Sunday). This will have a condition that if it is a business day it resolves to 0

5) Finally a boolean which will determine when the value should be pulled from the original line item to the new line item

Below you will find the blueprint details

So, I have a slightly different way of doing than Austin did, but will get you home nonetheless.

Create a list of Days (Monday, Tuesday, Wednesday, etc...) with the code being the day of the week.

Create a SYS Days Properties module with a line item called Add Days. This will be entered by the admin, 2 for Saturday, 1 for Sunday:

Create a SYS Time Day Module which is dimensonalized by Time at the Day level.

Line items:

Days of Week
- formula: weekday(start()) formatted as Number
Day List
- formula: finditem(Days, Text(Days of Week)) formatted as List
Day To Send Money - this could also be used for the last day money was sent
- formula: IF DAY(START()) = 10 THEN START() + SYS Days Properties.Days to Add[LOOKUP: Day List] ELSE PREVIOUS(Day to Send Money)
  - formatted as Date
Send Money? - not sure if you need this, but if you want to filter the days that money was sent, this would be a good option.
- formula: START() = Day to Send Money
  - fomatted as Boolean

All comments

Hello,

There are 5 line items you will need to set up. I've attached screenshots below:

1) A line item that is populated with the current date. This will be the input to the logic described below

2) A line item that uses the DAY() formula which returns the day # of the month

3) A line item that uses the WEEKDAY() formula to return the number of the day of the week it is. (Monday = 1, Sunday = 7)

4) A line item which calculates days till next business day (if the 10th falls on a Saturday or Sunday). This will have a condition that if it is a business day it resolves to 0

5) Finally a boolean which will determine when the value should be pulled from the original line item to the new line item

Below you will find the blueprint details

So, I have a slightly different way of doing than Austin did, but will get you home nonetheless.

Create a list of Days (Monday, Tuesday, Wednesday, etc...) with the code being the day of the week.

Create a SYS Days Properties module with a line item called Add Days. This will be entered by the admin, 2 for Saturday, 1 for Sunday:

Create a SYS Time Day Module which is dimensonalized by Time at the Day level.

Line items:

Days of Week
- formula: weekday(start()) formatted as Number
Day List
- formula: finditem(Days, Text(Days of Week)) formatted as List
Day To Send Money - this could also be used for the last day money was sent
- formula: IF DAY(START()) = 10 THEN START() + SYS Days Properties.Days to Add[LOOKUP: Day List] ELSE PREVIOUS(Day to Send Money)
  - formatted as Date
Send Money? - not sure if you need this, but if you want to filter the days that money was sent, this would be a good option.
- formula: START() = Day to Send Money
  - fomatted as Boolean

very very helpful. Thank you guys!

yes, I got it and applied it to my formula and works great. ty again

Quick Links

Can you help?

Unanswered questions

Different Subsets of Parent Lists in Input & Output Modules
I have 2 lists used in modelling, In Input module I'm using subset of territories from Country List and subset of Regions from Flat list. But in Output module,Its reversed, I'm using subset of regions from Country list and subset of territories from flat list.How to derive the numbers from input to output module. I do have…
Is there a way to turn off the Show Comments Summary panel in UX pages with Comments?
This shows all comments across all data to all users with access to the UX page. This is not appropriate for any UX which might contain security access sensitive data. Comments on grids and cards ARE security dependent. That works fine. But anyone with access to the page can open this Summary Panel and see ALL comments…
Are there any plans to support actions to rename or delete native Versions?
If anyone is aware of idea exchange ideas that are currently being evaluated by the product team, if you don't mind linking them here, would love to give them an upvote. Thanks!