Week of the Year in report

Hi Jarno,

Unfortunately, there is no easy way to accomplish this in Yellowfin. Typically, when using a date part formatter such as this, Yellowfin will call the relevant DBMS'es prebuilt function, such as MySQL's YEARWEEK().

You may have luck employing a custom function to adjust the output of this call based on whether the specific year is a year that rolls over at 52. A guide on custom functions can be found here:

http://wiki.yellowfin.com.au/display/USER72/Custom+Functions

In the end it may be easier to live with the limitations of the workaround that you have already found. Please let me know if you have any questions on this.

Regards,

Nathan

Hi Jarno,

Any luck in resolving this?

Regards,

Nathan

Hi Nathan,

Thanks for your response. We decided to live with the "incorrect" weeks for now, so to keep using the data formatter. We did not have time yet to look at the custom function, and the drawback of losing the time series functionality is a too big disadvantage for us.

However, i still don't get why I get this offset, as when I try the pre-build function of our DBMS we do get the correct week number. Our data source is Redshift and using select date_part(week, '2016-12-26') results in 52, while Yellowfin displays 53. To be sure I also tried the MySQL's YEARWEEK() as that is our configuration database, and there the result is 201652. So what am I doing wrong here?

Kind regards,

Jarno

Hi Jarno,

I am not sure, I will look into that further.

The Week PostgreSQL pre-defined function uses the date_part Postgres function, so this should provide the result that you are interested in.

These functions, as well as any custom functions you add to this, can be found in the "Pre-Defined" section of the calculated field builder:

However, this does return a numeric field, which will prevent you from employing the time series functionality.

If the time series functionality is more important than the appropriate week, you may be out of luck. If not, this function seems like the easiest option. Please let me know your thoughts and I will continue to investigate how exactly the underlying date-part formats function.

Regards,

Nathan

Hi Jarno,

I finally got a chance to discuss this with my colleagues and they are 90% sure that we use a pre-built java function to accomplish this (rather than the DBMS function I initally assumed we used). Unfortunately, I have not found a better work around than the custom function provided below.

I am going to close this case for now but if you have any more questions on this, please just respond and the case will be re-opened.

Sorry I could not be of more help on this.

Regards,

Nathan

Hi Nathan,

Thanks for your response. The time series functionality is more important for us, so we will keep using this. Can I raise an enhancement request to be able to use the ISO 8601 standards for the week of the year in yellowfin?

Kind regards,

Jarno

HI Jarno,

I have switched this over to an idea so that the community has an opportunity to vote on it. At the end of each month the product team will review all pending ideas and select the most feasible and popular for future inclusion.

Let me know if you have any further questions.

Regards,

Nathan

Hello Experts,

Do we have any update on this thread? is this included in some newer fixes of YF?

Appreciate your response!

Thanks,

Amir

Hi,

Please respond to my query.

Thanks,

Amir

Hi Amir,

Thanks for reaching out on this. We are currently looking into the possibility of doing this within Yellowfin currently. Once I have an update I will be sure to provide it to you here!

Best,

Jared

Hi Jared,

Thank you for your response.

More details on the issue is as follows:

There is a predefined function "week" ARJDBC in Smart Reporting.

It returns the calendar week.

However, internally it uses the datepart function with the "week" argument:

select DATEPART(Week, DATEADD(SECOND, 1612443862+3600, '1970-01-01'))

results in 6 for the Feburary 4.

In Europe we work with "iso_week" because "week" can result in 53 weeks in a year. In Europe we always have 52.

So the function should be:

select DATEPART(ISO_WEEK, DATEADD(SECOND, 1612443862+3600, '1970-01-01'))

resulting in 5 for Feburary 4.

The customer tried two workarounds without success:

1. Modified custom-functions.xml.

2. dbfn('datepart','ISO_WEEK',dbfn('dateadd','SECOND',dbfn('convert','int',`CHG:Infrastructure Change`.`Submit Date`),'1970-01-01'))

I have found an IDEA on yellowfin:

https://community.yellowfinbi.com/topic/week-of-the-year-in-report

But maybe there is already some new way to force Smart Reporting to use "iso_week" instead of "week"

Please advice.

Thanks,

Amir

Hi Jared,

Any update on this thread.

Appreciate your response!

Thanks,

Amir

Hi Jared,

Could you please respond.

Regards,

Amir

Hi Amir,

Apologies for the delay in my response here!

I have been testing around this functionality and have found that it is possible to do this by creating a Date Function on the date field within the View Builder: https://wiki.yellowfinbi.com/display/yfcurrent/Date+Functions

Let me know if you have any questions regarding this.

Best,

Jared

Hi Jared,

Thank you for your response.

Could you please provide an example how we can achieve customer's requirement shared earlier.

Customer has already tried few steps shared on my previous update without success.

Appreciate your response

Thanks,

Amir

Hi Jared,

Appreciate your inputs on this.

Thanks,

Amir

Hi Amir,

The use case you described looks as though it uses a database function that exists within the RDBMS side!

If that is the case we should be able to create a custom function for this: https://wiki.yellowfinbi.com/display/user80/Custom+Functions

If you are looking for examples of custom date functions, these can be found within the custom-functions.xml file located at <Yellowfin>/appserver/webapps/ROOT/WEB-INF

Best,

Jared

Hi Jared,

It seems you missed my older updates.

Customer already tried using the custom functions but still no luck.

I would appreciate if you could try it inhouse and send the details with example.

Thanks,

Amir

Any update?

Thanks,

Amir

Hi Jared,

If you are not available to work on this, I would request you to assign this ticket to someone else.

Appreciate your help on this.

Thanks,

Amir

Hi Amir,

Apologies for any delay in our response here!

I've been testing around this and believe we need to address this via an Enhancement request. As such I've raised this as an enhancement request to our Dev team to review this further. Any updates on this I will be sure to provide here.

Let me know if you have any questions regarding the enhancement process or the task itself.

Best Regards!

Jared

Hi Jared,

Can you provide any updates on this issue?

Thanks,

Andreas

Hey Andreas,

Unfortunately at this time I am unable to provide an ETA. I will keep this ticket updated with any progress made.

Thanks,

Jared

Alrighty - thank you!

I don't understand what exactly Yellowfin is planning to do here.

Could you elaborate please?

Hey Andreas,

It looks like the devs are still investigating what it is will be needed to implement this functionality.

Once they've assessed this they can begin working on the necessary changes needed to put it in place. Once this is done, the changes will be included within a new release of Yellowfin.

Best,

Jared