Question about Calculated Fields
28 September, 2009
I have a question about calculated fields. Currently, one of our clients is having trouble creating a calculated field that minuses two
Date fields to find the difference. These fields are labeled as; Date Closed and Date Recorded (both of these fields have the Date data type
in MySql and Yellowfin). The formula; Date Closed , a minus then Date Recorded was entered. When they clicked on Validate Sql hyperlink -
the statement (Sql is Valid) is presented but when the client clicked on Save and Close hyperlink - the Statement changed to Sql is Invalid
and did not let the user save the calculated field. I recreated the error on my login / computer when I went to create a calculated field
in the report. The weird thing is, when I created a calculated field in the view instead of the report (I used the same syntax and same
formula notation (Simple Formula)) it worked. I was wonder if Yellowfin has seen this problem before and if there was a easy solution for this problem. Attached, is a screen shot from the client of the client creating the calculated field in a Yellowfin report.
Date fields to find the difference. These fields are labeled as; Date Closed and Date Recorded (both of these fields have the Date data type
in MySql and Yellowfin). The formula; Date Closed , a minus then Date Recorded was entered. When they clicked on Validate Sql hyperlink -
the statement (Sql is Valid) is presented but when the client clicked on Save and Close hyperlink - the Statement changed to Sql is Invalid
and did not let the user save the calculated field. I recreated the error on my login / computer when I went to create a calculated field
in the report. The weird thing is, when I created a calculated field in the view instead of the report (I used the same syntax and same
formula notation (Simple Formula)) it worked. I was wonder if Yellowfin has seen this problem before and if there was a easy solution for this problem. Attached, is a screen shot from the client of the client creating the calculated field in a Yellowfin report.
I have just tried creating a similar calculated field on a report, and it saved successfully. This was using a MySQL 5.0 database.
However, the calculations were not correct, because (1) Yellowfin was not correctly identifying the returned value as numeric, and (2) the numbers returned by the database weren't the expected values. From the MySQL documentation is looks like the correct way to calculate the difference between two dates is to use the DATEDIFF(date1, date2) function.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff
There are two ways to achieve this in Yellowfin. The easiest is to use an Advanced Function on the report. Drag both dates into your report, select the end date and click the Advanced Function button. Select the "Days Between Date" function and then select the start date. The end date will now be replaced by the days difference. Note that this conversion is done after the result set is returned from the database, so the difference can not be used for aggregation, filters, etc.
The other way is to use a custom forumula template. This takes a little more work to set up, but is more flexible. You will need to edit the file Yellowfin/appserver/webapps/ROOT/WEB-INF/custom-
functions.xml.
Add a function at the end of the file, such as this:
Days Between, MySQL
1
Start Date
datetime
2
End Date
datetime
datediff($2, $1)
datediff($2, $1)
MYSQL
numeric
You will need to restart Yellowfin to pick up the change. Now in the report builder, create a new calculated field and select "Pre-defined Formula" in the "Formula Type" drop-down. You should now be able to select the new "Days Between" function, and select your start/end dates.
Hope that helps, let us know how you go.
However, the calculations were not correct, because (1) Yellowfin was not correctly identifying the returned value as numeric, and (2) the numbers returned by the database weren't the expected values. From the MySQL documentation is looks like the correct way to calculate the difference between two dates is to use the DATEDIFF(date1, date2) function.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff
There are two ways to achieve this in Yellowfin. The easiest is to use an Advanced Function on the report. Drag both dates into your report, select the end date and click the Advanced Function button. Select the "Days Between Date" function and then select the start date. The end date will now be replaced by the days difference. Note that this conversion is done after the result set is returned from the database, so the difference can not be used for aggregation, filters, etc.
The other way is to use a custom forumula template. This takes a little more work to set up, but is more flexible. You will need to edit the file Yellowfin/appserver/webapps/ROOT/WEB-INF/custom-
functions.xml.
Add a function at the end of the file, such as this:
Days Between, MySQL
1
Start Date
datetime
2
End Date
datetime
datediff($2, $1)
datediff($2, $1)
MYSQL
numeric
You will need to restart Yellowfin to pick up the change. Now in the report builder, create a new calculated field and select "Pre-defined Formula" in the "Formula Type" drop-down. You should now be able to select the new "Days Between" function, and select your start/end dates.
Hope that helps, let us know how you go.