Calculated Fields - Subtract Dates

Hi Paul,

Thanks for reaching out. Can you explain your actual use case for this? You can't subtract, for example, days, from a date field because you cannot perform arithmetic expressions between DATE/DATETIME and NUMERIC fields. To accomplish something along this lines will likely require drafting a Freehand SQL statement utilizing one of the RDBMS-specific date functions, e.g., DATEDIFF in Microsoft SQL Server, but if you can let me know what you're actually trying to do I can at least provide some pointers to get you headed in the right direction.

Regards,

Mike

Regards,

Mike

Hi Mike,

To preface, I am not a SQL expert, but I do have expert support here that I have used.

When we use the statement you suggested, I receive the following error:

SQL statement is invalid.

The error message returned from the database was:

ERROR: function datediff(date, timestamp with time zone) does not exist

Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Position: 9

Any help would be appreciated. Thanks!

Paul Janzen

Sr. Business Consultant – Investigation Services

Manulife

E paul_j_janzen@manulife.ca

T 519 747 7000 x242825

25 Water St. South

Kitchener, ON, N2G 4Z4

Benefits fraud hurts us all. Be part of the solution. manulife.ca/shareandprotect

Without standards, there can be no improvement.     Taiichi Ohno

From: Support Queue <support@Yellowfin.bi>

Sent: Thursday, October 24, 2019 6:13 PM

To: Paul J. Janzen <Paul_J_Janzen@manulife.ca>

Subject: New Comment in "Calculated Fields - Subtract Dates"

Hi Paul,

Thanks for your response. This was an example using Microsoft SQL Server. If your data source's RDBMS is different, which based on the "function... does not exist" error, this is almost certainly the case, this will require altering the syntax and/or a differently named function. You should be able to find this information under Application Details in your info.jsp file, which can be accessed by appending '/info.jsp' to your login URL. For example, localhost:8080/info.jsp. If you can provide this file to me I can see the db you're using (assuming you're not using a different db for data sources vs. what you're using for the configuration database), I can try and alter the statement for your particular case.

Regards,

Mike

Hi Paul,

I just wanted to check in and see how things are going with this.

Regards,

Mike

Hi Mike,

Unfortunately, I tried your suggestion and received the following error:

Paul Janzen

Sr. Business Consultant – Investigation Services

Manulife

Epaul_j_janzen@manulife.ca

T 519 747 7000 x242825

25 Water St. South

Kitchener, ON, N2G 4Z4

Benefits fraud hurts us all. Be part of the solution. manulife.ca/shareandprotect

Without standards, there can be no improvement.     Taiichi Ohno

From: Support Queue <support@Yellowfin.bi>

Sent: Tuesday, November 5, 2019 1:24 PM

To: Paul J. Janzen <Paul_J_Janzen@manulife.ca>

Subject: New Comment in "Calculated Fields - Subtract Dates"

Hi Paul,

Thanks for your response. If the file were accessible, I'd think it'd be manulife.i-sight.com/info.jsp not manulife.i-sight.com/login/info.jsp, but there's a good chance the file's inaccessible regardless.

There's another way to find this information, however. You can find these details from within the application under Administration > System Information > Application Details.

Regards,

Mike

Hi Mike,

Under Administration, I only have access to User Groups and Content Folders.

I am starting to think that I need to contact i-Sight – the vendor that we were given Yellowfin from.

If you don’t have any other suggestions, then that is what I am going to do.

Paul Janzen

Sr. Business Consultant – Investigation Services

Manulife

Epaul_j_janzen@manulife.ca

T 519 747 7000 x242825

25 Water St. South

Kitchener, ON, N2G 4Z4

Benefits fraud hurts us all. Be part of the solution. manulife.ca/shareandprotect

Without standards, there can be no improvement.     Taiichi Ohno

From: Support Queue <support@Yellowfin.bi>

Sent: Wednesday, November 6, 2019 11:48 AM

To: Paul J. Janzen <Paul_J_Janzen@manulife.ca>

Subject: New Comment in "Calculated Fields - Subtract Dates"

Hi Paul,

The ability to see the specified section does indeed depend on permissions assigned to your role. You'd also be able to find this information in your Data Source's Connection Settings, but if you're only seeing User Groups and Content Folders under Administration that won't be an option either. As such, if no one internally knows what db your data source is built on and you don't have the permissions to find this information out, then yes, I'd assume I-Sight would be able to inform you of what RDBMS is being used. At which point you can then alter the query provided earlier to the syntax specifications of the database type that's being used.

Regards,

Mike

Hi Paul,

I just wanted to check in and see how things are going with this. Do you need anything else from my end here?

Regards,

Mike

Hi Paul,

I'm going to go ahead and mark this one as Answered since I haven't heard back from you and I'm afraid Support can't assist much further besides, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to assist further.

Regards,

Mike