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

Azure SQL Connection, database name with Hyphens in Data Orchestrator
I am attempting to connect to an azure SQL database vis at the MSSQL connector in data orchestrator but am having issues and I believe it is due to hyphens in database name (which is a fairly standard naming convention I will note!) Example below (fake credentials) - has anyone else encountered similar and is there a way…
12.4.1 Activity: Create Saved View for Non Employee Expenses
Hi, for some reason my shipping costs are the same over every month and I don't know what to do to fix it.
Workaround for Formula & Closing Balance summary methods together in Polaris
Hello folks, Does anyone know how to work around the Polaris limitation of using the Formula or Ratio summary method alongside the Closing Balance within the same line item? I have a Business Unit list where the total (All BUs) must display the Corporate (1 of 20 leaf items under All BUs total) number of Unique Customers.…