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

OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385