Week of the Year in report

Jarno Pons shared this idea 4 years ago
Idea Logged

I am using the date part formatter in a report, to display a date as Week of the Year. For 2016 the weeks range from 1 to 53, while in our country and the ISO 8601 standards, 2016 only has 52 weeks. Is there any way to change the calendar format that Yellowfin uses for this mask? I do not see any option in the regional settings.


Workaround is of course to calculate the week number myself, but the major drawback is that I cannot use this calculated field as a time series in a chart.


Kind regards,

Jarno

Comments (21)

photo
1

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

photo
1

Hi Jarno,


Any luck in resolving this?


Regards,

Nathan

photo
1

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

photo
1

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.

48dae432a96ef5d268701a5d18cb13fb

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:

a3e85f599281cc23b686df9c2b106ebe

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

photo
1

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

photo
1

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

photo
1

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

photo
1

Hello Experts,

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

Appreciate your response!

Thanks,

Amir

photo
1

Hi,


Please respond to my query.


Thanks,

Amir

photo
1

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

photo
1

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

photo
1

Hi Jared,


Any update on this thread.

Appreciate your response!

Thanks,

Amir

photo
1

Hi Jared,

Could you please respond.

Regards,

Amir

photo
1

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

photo
1

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

photo
1

Hi Jared,

Appreciate your inputs on this.

Thanks,

Amir

photo
1

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

photo
1

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

photo
1

Any update?


Thanks,

Amir

photo
1

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

photo
1

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