First working day-Monday of 'current year - 1' if it's weekend

Mihajlo

Hello everyone,

In one of our analysis we have comparison of a date chosen in filter (YYYY-MM-DD) and same date previous year. The problem arises if that day previous year falls into weekend (Saturday or Sunday) because there's no data. What we need is a function or some kind of TIMESTAMPADD (if it exists) that would return the first working day i.e. first Monday after that date previous year (date chosen in filter - 1year), meaning if that date previous year was f.e. saturday then add 2 days and return monday because otherwise there's no data and column for the previous year is empty...Is there any solution for this ? Thanks in advance

P.S.: Current function for that column is FILTER("T1"."C1" USING ("T1"."C2" = TIMESTAMPADD(SQL_TSI_YEAR, - 1, cast(@{pvar_date}{date '2020-09-01'} as date)))), but when, in this case,  2019-09-01 is Saturday or Sunday we get no data...

Kind regards,

Mihajlo

Joel

The ideal solution to this problem would be toextend your time dimension with these attributes. You could have a flag on the date row so that if the date is a weekend or weekday, this will be flagged appropriately or you could have another attribute that tells you the next working date. If the day is Saturday for example then the new attribute would have a value for the following Monday etc.

When this is worked out in the dimension, you just pick the value and avoid the need for any complex logic in your analytical layer (OBIEE).

[Deleted User]

+1 to Joel. Forget trying to do that in a formula for one simple reasons which should be obvious to anyone working with any kind of data:

Calculation effort. If you put that into a formula, then that logic gets executed EVERY time the column is called for EVERY row within that query. There is hardly anything that's less efficient and more wasteful than that.

Not even starting with "What about leap years?" or the 500 other questions tied to date logic. Build a correct calendar that represents YOUR company's date logic.

Mihajlo

Hi Joel,

Thanks for the advice, we followed it and applied as a solution

BR, Mihajlo

Mihajlo

Hello Christian,

Thank you ! You are right, we solved in on DB level.

BR, Mihajlo