Hi Bart,

It does look like the calculations are doing something funny, so it would be interesting to see the report SQL Syntax behind the scenes.

I have run a few tests in 6.3, though cannot get my calc field working with SQL Server.

What database are you using, and what are the data types of the 2 columns used?

One last thing, as I'm sure you're aware that Yellowfin 6.1 is no longer supported.
So if we do find a defect we will not be able to apply a fix to 6.1, it can only be implemented in 6.2 + releases.

Regards,
David

Hi David -

Database is MySQL, and fields are both date formats.

I've attached more details, looks like the culprit for the simple formula calculated field approach is no format available for numeric.

Just a note that I ran the SQL scripts using freehand and received the same results.

Any other thoughts would be appreciated.

Thanks,
Bart
i-Sight at Customer Expressions

Hi Bart,

Thanks for the additional info, though I cannot see the report SQL?

Thanks,
David

Hi David - it was on the second page of the attachment but I've also pasted below.

One thing I don't understand is the last line of code '> -50', we didn't include this in any of the calc fields.

Hi Bart,

Thanks for that, I couldn't see the SQL in the previous attachment?

I'm running some tests in 6.3 with MySQL and date fields, but I cannot get any results returned. It seems that it cannot convert a date to an integer , e.g. 'Show date' or '0' , because they're 2 different data types, they cannot be displayed in the same column. Am I missing something here?

I'm going to try this in 6.1, and if it still fails, I will try with some different formats.

Also, regarding the -50, it does look strange, but I have seen SQL Server date diff syntax do similar things, and is just how it works.

Will give you an update tomorrow, but if you could also let me know more about your data types, it would be useful.

Thanks,
David

Hi Bart,

Ok it looks like you're using a view level calc field, which is why I couldn't replicate earlier.

I can now replicate and believe this is related to MySQL and the - formula you're trying to use.

Using the analytic function 'Days Between', is correct. As you have found.

Using the calc field and - , this does not do a date diff. It does something strange like count the number of minutes & secs between.

In terms of your -50 in your where statement, I guess we need to know what you're using as your filter and seeing why it translates to that in MySQL.

I assume you're getting the correct results in YF (minus the calculated field).

Regards,
David