Date Functions problem calculating weekdays

Phillip Lassen shared this question 8 months ago
Answered

Hello,

I am using Yellowfin Build 20180712 (see system information attached) with a Redshift as database. When I am using the Date Functions "Week End / Start Date" I am getting weird sql calculations. My table is called calendar and the field day is a date (e.g. "2018-03-03"). Within the Yellowfin configuration I choose Monday as week start date. What Yellowfin does:

Week Start Date turns into date_trunc('week', "calendar"."day") + interval '5 days'

Week End Date turns into date_trunc('week', "calendar"."day") - interval '1 day'

What I would have expected would be:

date_trunc('week', "calendar"."day") + interval '6 days'

date_trunc('week', "calendar"."day")


System Information Application Version: 7.4.6 Build: 20180712 Java Version: 1.8.0_181 Operating System: Linux 4.4.0-1063-aws (amd64)

I am looking forward fr you thoughts.

Best,

Phillip

Best Answer
photo

Hi Phillip,

As we know, Yellowfin takes Sunday as the start of the week, and Saturday as the end of the week.

Thus, if the DB function returns Monday 20th as the start, then YF will have to subtract 1 day to get Sunday 19th (20 -1 = 19), and it will have to add 5 to get Sat 25th (20 + 5 = 25).

This calculation seems to be the standard way of formatting this for SQL DB's, which is why this particular query is run as the default for Yellowfin.

Hopefully this makes it a little clearer.

Cheers,

Paul

Comments (8)

photo
1

Hi Phillip,

Thanks for reaching out. The Start Date of Monday in the configuration is used by the Filters and is not specifically a global setting, so the actual calculation is actually correct. In order to get around this, you can create a custom calculation with Freehand SQL and enter - Adjusting to suit your column and DB as required.

date_trunc('week', "Primary"."date") - interval '0 day'

date_trunc('week', "Primary"."date") + interval '6 days'

which will then in turn produce Monday as being the start of the week.

This is the calculated field I created.

9f158930872871b60fd6fbc6f1319f9a

This is what I see when I use the above calculated field

84e8a494bf9ff9fb04013aafd2a12e39

Hopefully this will act as a suitable solution for you.

Regards,

Paul

photo
1

Hi Paul,


thank you for detailed and fast answer.


- The Start Date of Monday in the configuration is used by the Filters and is not specifically a global Setting

I didn´t know that. Thank you for the Information.


- so the actual calculation is actually correct.

Could you explain the yellowfin calculation (+ 5days / - 0 days) more in Detail? When I look at a nother customer, using a memsql database, the End Date e.g. ist calculated differently (+ 7 days).


- In order to get around this, you can create a custom calculation with Freehand SQL and enter - Adjusting to suit your column and DB as required.

I already got to this point before I came to you, but this would effect a lot of views and reports.


Best,

Phillip

photo
1

Hi Phillip,

Could you explain the yellowfin calculation (+ 5days / - 0 days) more in Detail? When I look at a nother customer, using a memsql database, the End Date e.g. ist calculated differently (+ 7 days).

In all honesty I am not sure as I have seen 5, 6 and 7 under different circumstances, but I will request clarification on this from the DEV's, just leave this with me for a little while.

I already got to this point before I came to you, but this would effect a lot of views and reports.

If you need to change this across the reports, then unfortunately it would require a bit of work, but please note we do have an enhancement request in for this to respect the Global setting, but there is not ETA on it.

I will get back to you asap on the calc.

Thanks,

Paul

photo
1

Hi Paul,


I Need to make a correction. ThefFormular for memsql for the end of week date is INTERVAL 7 - DAYOFWEEK(`calendar`.`date`) DAY).


Still I am curious why Week End Date turns into date_trunc('week', "calendar"."day") - interval '1 day'.


Looking forward for you reply.


Best,

Phillip

photo
2

Hi Phillip,

As we know, Yellowfin takes Sunday as the start of the week, and Saturday as the end of the week.

Thus, if the DB function returns Monday 20th as the start, then YF will have to subtract 1 day to get Sunday 19th (20 -1 = 19), and it will have to add 5 to get Sat 25th (20 + 5 = 25).

This calculation seems to be the standard way of formatting this for SQL DB's, which is why this particular query is run as the default for Yellowfin.

Hopefully this makes it a little clearer.

Cheers,

Paul

photo
1

Hello Paul,


somehow I switched Start- end End Date. That was what confused me. I have again recreated the Szenario and now it appears exactly like you said. I am fine with that. Sorry for the confusion and thank you for your time.


Best,

Phillip

photo
1

Hi Phillip,

No problems at all. If you need anything else or if you are happy for me to close off the ticket, please let me know.

Regards,

Paul

photo
1

Hi Paul,

sure, feel free to close this ticket.

Best,

Phillip