Date Functions problem calculating weekdays

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.

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

Hopefully this will act as a suitable solution for you.

Regards,

Paul

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

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

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

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

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

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

Hi Paul,

sure, feel free to close this ticket.

Best,

Phillip