How to use Date Functions with Calculated Fields?

Hi Stephen,

This functionality only accepts date field types so you will need to provide it that somehow. Calculated fields are applied at report run time so the casting will not work for this.

You will need to derive a date field at the database level prior to using this function in the view.

Sorry for the inconvenience.

Regards,

Nathan

I went ahead and created my own calculated fields in place of the date functions (year and month) and configured the drill-down manually. Will post back here once I've tested if this is a viable work around.

Sounds good, let me know if you have any questions in the meantime.

As I mentioned, our dates are stored as integers in a SQL Server DB. To get them into dates in Yellowfin I create a calculated field using the formula below and then use the "Text to SQL Date" converter in Yellowfin:

CONVERT(DATE,CONVERT(VARCHAR(8),schedules.LDate))

In the meantime, I tried to work around by adding other calculated fields for the other date parts and building the drill down myself. At first I used the DATENAME function to get the actual names I wanted (April, August, Monday, Wednesday, etc.). This worked with the drill down, except that Yellowfin saw the data as text so they were sorted alphabetically when you drilled-down to month (i.e. April was first instead of January). Instead, I switched to using the DATEPART function and then used Yellowfin to format them using the Date Hierarchy Formatter:

DATEPART(mm, CONVERT(VARCHAR(8), Schedules.ldate))
DATEPART(dw, CONVERT(VARCHAR(8), Schedules.ldate))

Hi Stepen,

Glad you have something working. Regarding your month sort order issue, you can try to apply the "Month" reference code to get this sorting order back.

Regards,

Nathan

Hi Stephen,

I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!

Regards,

Nathan