How to use Date Functions with Calculated Fields?

Stephen Johnson shared this question 1 year ago
Answered

I saw the wiki article about how to set up a date hierarchy using date functions: http://wiki.yellowfin.bi/display/USER74/Creating+a+View#CreatingaView-DateHierarchyFields but this assumes that you have a date formatted field in your database to use.

In our database the dates are stored as integers in the format yyyymmdd. I have created a calculated field that converts this integer to a date, but I can't seem to use the date function to generate a date hierarchy using a calculated field.

Is there a way to leverage this functionality without having to change our database schema?

Comments (6)

photo
1

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

photo
1

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.

photo
1

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

photo
1

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))
It would be ideal if Yellowfin could use this as an input the date functions. Hopefully that will get added in the future.

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))

photo
1

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

photo
1

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