How to get the division quotiant and remainder using calculated field?

Mohammad Rehman shared this question 1 year ago
Answered

Hello Experts,

I am wondering and could not find a way to find the number of days, hours , minutes and seconds from two date difference or from available seconds. If there is a pre-defined function, please direct me ( I am aware of formating the seconds field to select Days Hours Minutes Seconds, to get the result in one field). I am looking basically to calculate in separate fields.

I know there are predefined formula to calculate the Days, Seconds but I want to calculate the days and then hours from remainder second's and then minutes from hours remainder seconds and remainder seconds.

TIA

Mohammad

Comments (7)

photo
2

Hi Mohammad,

Unfortunately these functions do not exist at the report level, though it should be relatively easy to create your own custom functions to achieve this.

I would recommend finding the current date-diff functions in the custom functions file and editing these to suit your needs. These can be found:

youYfInstall/appserver/webapps/ROOT/WEB-INF/customfunctions.xml

Let me know if this sounds like it will work.

Nathan

photo
1

Hi Nathan,

Thank you as always for your time and help.

I will try to play with customfunction.xml and see where i end up.

Regards,

Mohammad

photo
2

Hi Mohammad,

Sounds good, let me know if you run into any problems or have any questions!

Nathan

photo
1

Hi Nathan,

I did run into problem, i added the new function for mod($1/$2) when try to use it i got the following error in reporting logs. I wanted to add mod and floor function to calculate the quotient and remainder.

It seems new oracle functions except mentioned below in error.

Caused by: java.sql.SQLException: Unsupported function: mod. Supported functions are: COUNT, AVG, MIN, MAX, SUM,CURRENTDATE, DATE, TIME, DAY, MONTH, YEAR, WEEK,WEEKDAY, HOUR, MINUTE, SECOND, QUARTER, DATEDIFF, DATENAME, DATENUM, DATEADD,UPPER, LOWER, LTRIM, RTRIM, REPLACESTR, LPAD, RPAD, SUBSTR


Thanks

Mohammad

photo
2

Hi Mohammad,

That is odd as I have certainly used other functions not on that list in custom functions. However, I was not using Oracle.

One thing that I have seen work, is the use of DB level functions. If you create this function in the DB, you should be able to reference that function in your custom Yellowfin function, or possibly in a free-hand sql calculated field.

If not then your best bet might be to look into coding a custom advanced function (if you are familiar with Java)

Regards,

Nathan

photo
1

Hi Nathan,

Appreciate your reply and Thanks as always for your help and time.

I was away for a while and could not login to check your reply.

I will test using db function to see if that helps.

Regards,

Mohamad

photo
2

Sounds good, let me know if you run into any problems.

Nathan

photo