Quiestion on formula for IF and And functions
Answers
-
Hi Rene,
The Monarch Help Guide may provide some guidance on how to create IF and AND functions: https://docs.datawatch.com/monarch/desktop/ht_table_createexp.htm?rhsearch=Reg%20expressions&rhsyns=%20
Found other helpful links within the Monarch Help Guide:
- List of User Defined Functions : https://docs.datawatch.com/monarch/desktop/List_of_User-Defined_Functions.htm?rhsearch=IF%20functions&rhsyns=%20
- List of Function Reference: https://docs.datawatch.com/monarch/desktop/Functions_Reference.htm?rhsearch=IF%20functions&rhsyns=%20
- Overview of User Defined Functions: https://docs.datawatch.com/monarch/desktop/User-Defined_Functions_Overview.htm?rhsearch=IF%20functions&rhsyns=%20
- Creating Compound Expressions: https://docs.datawatch.com/monarch/desktop/Creating_compound_expressions.htm?rhsearch=operational%20expressions&rhsyns=%…
You may also reach into Support whom might be able to provide additional guidance. https://www.datawatch.com/support/
0 -
Here is the literal translation of your excel formula to monarch:
One line version:
IF($G2>$AL$1 .and. $AJ2="", $AL$1-$R2, IF($G2<$AL$1 .and. $AJ2="", $AL$1-$G2, IF($AJ2="", $AL$1-$R2, 0)))
Multi-line version:
IF($G2>$AL$1 .and. $AJ2="",
$AL$1-$R2,
IF($G2<$AL$1 .and. $AJ2="",
$AL$1-$G2,
IF($AJ2="",
$AL$1-$R2,
0)
)
)
Since your excel version appears to reference cells on different rows, I don't think you are going to get the results you expect. When building a formula field, you need to reference the field names (not cell addresses), and generally speaking you can only perform calculations between fields on the same row (you cannot look 1 row down or up).
0 -
Altair Forum User said:
Here is the literal translation of your excel formula to monarch:
One line version:
IF($G2>$AL$1 .and. $AJ2="", $AL$1-$R2, IF($G2<$AL$1 .and. $AJ2="", $AL$1-$G2, IF($AJ2="", $AL$1-$R2, 0)))
Multi-line version:
IF($G2>$AL$1 .and. $AJ2="",
$AL$1-$R2,
IF($G2<$AL$1 .and. $AJ2="",
$AL$1-$G2,
IF($AJ2="",
$AL$1-$R2,
0)
)
)
Since your excel version appears to reference cells on different rows, I don't think you are going to get the results you expect. When building a formula field, you need to reference the field names (not cell addresses), and generally speaking you can only perform calculations between fields on the same row (you cannot look 1 row down or up).
Thank you very much. For your help, I appreciate it.
Regards,
Rene Tan
Accountant II
Great Lakes Credit Union
Accounting Department
Ph 847-578-7384
Fax 847-578-7036
Follow us!
<http://www.glcu.org/>[facebook]<http://www.facebook.com/GreatLakesCU>[Twitter]<http://www.twitter.com/GreatLakesCU>
NOTICE: Information contained in this transmission to the named addressee is proprietary information and is subject to privilege and work product confidentiality. If the recipient of this transmission is not the named addressee, the recipient should immediately notify the sender and destroy the information transmitted without making any copy.
0 -
Altair Forum User said:
Here is the literal translation of your excel formula to monarch:
One line version:
IF($G2>$AL$1 .and. $AJ2="", $AL$1-$R2, IF($G2<$AL$1 .and. $AJ2="", $AL$1-$G2, IF($AJ2="", $AL$1-$R2, 0)))
Multi-line version:
IF($G2>$AL$1 .and. $AJ2="",
$AL$1-$R2,
IF($G2<$AL$1 .and. $AJ2="",
$AL$1-$G2,
IF($AJ2="",
$AL$1-$R2,
0)
)
)
Since your excel version appears to reference cells on different rows, I don't think you are going to get the results you expect. When building a formula field, you need to reference the field names (not cell addresses), and generally speaking you can only perform calculations between fields on the same row (you cannot look 1 row down or up).
I just wanted to add to Chris's post where he mentioned you cannot use another row. It looks like in your case you may have a workaround as you have $AL$1 so you are always looking up to AL1 no matter the row, if that is true, create a formula field and make it a constant.
For example if AL1 is a date, you can have the formula field as a hard coded date or use the date functions to create that specific date using Today() and DateAdjust() or end_of_current_month() or previous.
If its a number just type in the number.
Just an idea, not sure what is in AL1 but you might be able to have a work around even if you need to change the constant each time.
Hope you can get your formula to work.
Patrick
0 -
Altair Forum User said:
I just wanted to add to Chris's post where he mentioned you cannot use another row. It looks like in your case you may have a workaround as you have $AL$1 so you are always looking up to AL1 no matter the row, if that is true, create a formula field and make it a constant.
For example if AL1 is a date, you can have the formula field as a hard coded date or use the date functions to create that specific date using Today() and DateAdjust() or end_of_current_month() or previous.
If its a number just type in the number.
Just an idea, not sure what is in AL1 but you might be able to have a work around even if you need to change the constant each time.
Hope you can get your formula to work.
Patrick
Hi, Patrick.
I use constant value for previous end of month. Do you a formula on how I can automate this process instead of manually entering the constant date value.
Regards,
Rene Tan
Accountant II
Great Lakes Credit Union
Accounting Department
Ph 847-578-7384
Fax 847-578-7036
Follow us!
<http://www.glcu.org/>[facebook]<http://www.facebook.com/GreatLakesCU>[Twitter]<http://www.twitter.com/GreatLakesCU>
NOTICE: Information contained in this transmission to the named addressee is proprietary information and is subject to privilege and work product confidentiality. If the recipient of this transmission is not the named addressee, the recipient should immediately notify the sender and destroy the information transmitted without making any copy.
0 -
Altair Forum User said:
Hi, Patrick.
I use constant value for previous end of month. Do you a formula on how I can automate this process instead of manually entering the constant date value.
Regards,
Rene Tan
Accountant II
Great Lakes Credit Union
Accounting Department
Ph 847-578-7384
Fax 847-578-7036
Follow us!
<http://www.glcu.org/>[facebook]<http://www.facebook.com/GreatLakesCU>[Twitter]<http://www.twitter.com/GreatLakesCU>
NOTICE: Information contained in this transmission to the named addressee is proprietary information and is subject to privilege and work product confidentiality. If the recipient of this transmission is not the named addressee, the recipient should immediately notify the sender and destroy the information transmitted without making any copy.
I'm assuming your asking for the previous month end date?
create a new formula field and make the type Date/Time and format as you wish (you can hide this column if it is not needed to bee shown on the final report).
For the formula itself, I typically like using a date from the input report but not sure what your data has. If you have a current month date or previous month date always in your input I would use that as a basis, most of my reports have a post date or report run date so I have been lucky, this way if you open a report from a month or two ago you don't have issues calculating the prior month. If you have a current month date End_Of_Previous_Month(Date from report) or if you have a prior month date End_Of_Current_Month(Date from report)
If you do not have anything you can use in the input report and you are always looking for the current day's previous month end date the use End_Of_Previous_Month(today())
Hope this makes sense and helps you,
Patrick
0 -
Altair Forum User said:
I'm assuming your asking for the previous month end date?
create a new formula field and make the type Date/Time and format as you wish (you can hide this column if it is not needed to bee shown on the final report).
For the formula itself, I typically like using a date from the input report but not sure what your data has. If you have a current month date or previous month date always in your input I would use that as a basis, most of my reports have a post date or report run date so I have been lucky, this way if you open a report from a month or two ago you don't have issues calculating the prior month. If you have a current month date End_Of_Previous_Month(Date from report) or if you have a prior month date End_Of_Current_Month(Date from report)
If you do not have anything you can use in the input report and you are always looking for the current day's previous month end date the use End_Of_Previous_Month(today())
Hope this makes sense and helps you,
Patrick
Yes, the previous month end date.
Regards,
Rene Tan
Accountant II
Great Lakes Credit Union
Accounting Department
Ph 847-578-7384
Fax 847-578-7036
Follow us!
<http://www.glcu.org/>[facebook]<http://www.facebook.com/GreatLakesCU>[Twitter]<http://www.twitter.com/GreatLakesCU>
NOTICE: Information contained in this transmission to the named addressee is proprietary information and is subject to privilege and work product confidentiality. If the recipient of this transmission is not the named addressee, the recipient should immediately notify the sender and destroy the information transmitted without making any copy.
0
Altair Employee