Forum

This content is now out of date.

Visit Our Community

Calculated Date Field and drop the year.

I am trying to take a date field and add 3, 6, 9 months and then drop the year.

I cannot figure out how to do this in Yellow Fin. I am using the following SQL:

Select Clients.client_id As clientid, Clients.first_name As ClientFN,
Clients.last_name As Cient_LN, Clients.date12, Clients.client_status,
Clients.case_manager_emp_id, EmployeesView.first_name, EmployeesView.last_name,
dateadd(month,3,Clients.date12) AS f_qtr_rev,
dateadd(month,6,Clients.date12) AS s_qtr_rev,
dateadd(month,9,Clients.date12) AS t_qtr_rev
From Clients Left Outer Join
EmployeesView On Clients.case_manager_emp_id = EmployeesView.emp_id
Where Clients.client_status = 'Active' order by case_manager_emp_id


The output is correct but I cannot figure out how to format the new fields (f_qtr_rev, s_qtr_rev,t_qtr_rev) so the year will be dropped.

I would really appreciate any help with this.
Thank You.
Hi There,

Assuming you are using calculated fields to create f_qtr_rev, s_qtr_rev, and t_qtr_rev you could change the format of the field to drop the yyyy.

This is what I did to accomplish what I believe you are trying to achieve:

1. Create a calculated field that used the DATEADD SQL function to add 3 months to the invoiced date field:


Forum image


2. Edit the format of the 3month column:


Forum image


3. Under Display, change Date Format to Other:


Forum image


4. Change Custom Date Format to dd/MM and save:


Forum image


5. Your date should now appear without the yyyy:


Forum image


Please let me know if this is what you are looking to accomplish. We hope to hear back soon!

Kind Regards,

Dustin

Best regards,

The Yellowfin Support Team

Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4










I can't seem to get the custom sql formula to add 3 months. Would you mind showing the formula you used to get the output in your print screen? Thank you so much!
Hi Again,

Here is a link to a forum post that I recently wrote covering the dateadd function. I should have included it in my previous post! Apologies!

Please let me know if you have any questions regarding it:

Current Day Plus 30 Days

For your particular calculated field you would just need to the dadeadd function to be:

dateadd('month',3,Your Date Field)

Please let me know if you have any other questions!

Kind Regards,

Dustin

Best regards,

The Yellowfin Support Team

Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
Thank you so much for quick responses! I am not sure what I am doing wrong, but I can't get the above formula to work. Please see the print screen.
Thank you so much for quick responses! I am not sure what I am doing wrong, but I can't get the above formula to work. Please see the print screen.
These functions can be finicky. I have a couple questions:

1. Is annual_review_date considered a date (not a VARCHAR) in your database?

2. Can you try DATEADD(month,3,annual_review_date)--without the '' around month

3. If that doesn't work, could you let me know what database and version of Yellowfin you are using?

I look forward to hearing back.

Kind Regards,

Dustin

Best regards,

The Yellowfin Support Team

Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
I have a hunch you may be using SQL server due to the error you received. Here is what I found regarding Invalid parameter 1 specified for dateadd:

Passing a different value for each parameter not expected by the DATEADD function will generate a different error message:

SELECT DATEADD('DD', 10, GETDATE()) -� DatePart Parameter in Single Quotes

Msg 1023, Level 15, State 1, Line 1
Invalid parameter 1 specified for dateadd.

SELECT DATEADD(MIN, 10, GETDATE()) -- Invalid DatePart Value

Msg 155, Level 15, State 1, Line 1
'MIN' is not a recognized dateadd option.

SELECT DATEADD(DD, 1, '2012/04/31') -- Invalid Date

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.


Kind Regards,

Dustin

Best regards,

The Yellowfin Support Team

Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
The data type was date and I made it mandatory. Can you think of any way I can get this report done? Thank you!
Did this query work for you?

"2. Can you try DATEADD(month,3,annual_review_date)--without the single quotes around month"

Also, It would be helpful to know what database and version of Yellowfin you are using so that I can troubleshoot in your environment.

Kind Regards,

Dustin

Best regards,

The Yellowfin Support Team

Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4
The timestampadd function also worked for me:

timestampadd(sql_tsi_month, 3,invoiceddate)


Forum image


Kind Regards,

Dustin

Best regards,

The Yellowfin Support Team

Contact Us:
Email: support@yellowfin.bi
Wiki: wiki.yellowfin.com.au/display/USER71/Home
Community Forum: www.yellowfinbi.com/YFForum.i4

Thanks for trying Yellowfin

Please complete the form below to request your copy of Yellowfin today.