How can I display the date of last Friday for a set of data?

Question

Hi, I want to fetch data for 14 days from current date. When I fetch data, it should be in weekly basis. That is the date must be the friday for respective week.

If I run data today(04.02.2022) then I have to get data from 21.01,2021 to 03.02.2022(for 2 weeks). But the date should be 28.02.2022 (for data that's created from 21 Jan to 28 Jan) and for the next week it should be 04.02.2022 (for the data that are created from 29 Jan to 4 feb).

Pls help

Thanks in advance

Monalisha K · Answer

okay. Will try that in excel. Thank you

Gianni Ceresa · Answer

Ah, got it now.

So your logic is something like: for a report run today you want to have the date of Friday of this week and the date for Friday a week ago.

What happen if you run the report on a Tuesday? Do you still get the date of this week's Friday (so a date in the future)? Or do you want the date of the last 2 Fridays with a date <= the date you run your report?

And the suggestion to use Excel to show your logic is a way for you to get the answer to your own question.

If in Excel you set a column with some date and that's the date when you run your report, start writing some Excel formulas on the other columns to get the 2 dates you are after. Once you have the logic in Excel you will easily be able to translate it to OBIEE.

As I said OBIEE has a set of functions to calculate and manipulate dates, your need is all about finding the right piece of logic calculating what you are after.

Monalisha K · Answer

Sorry for the wrong info. If we "run your report" on January 7, the "last two week" will be December 25 and January 7. In that case, '31st dec' should be displayed as reporting date for the data from 25th Dec to 31st dec and 7th Jan should be displayed for the data from 1st Jan to 7th Jan.