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

Monalisha K

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

Gianni Ceresa

Hi,

Honestly I don't understand much of what you are asking...

Mainly because today is mainly Feb 3 still (except for Australia and some places there around), still I don't know where the Friday jump into your logic. The rest of your example is about end of February date that should be between Jan 21 and Jan 28).

Maybe you would like to use Excel to "visualize" in a little table what you have in mind as logic? (a tabular view of your logic is maybe more clear than words)

Anyway, OBIEE has various functions to do things with dates. It's all about finding the right logic with the available functions. As you talk about "Friday" you probably will need DAYOFWEEK(...) and to move across time TIMESTAMPADD(...) is probably what you will need as well.

Gianni Ceresa

Reply to this thread, don't open a new one.

If you "run your report" on January 7 you expect the "last two week" to be January 25 and February 7? What kind of calendar is that? How come that end of January and beginning of February are the "last two week data" for January 7?

Monalisha K

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.

Gianni Ceresa

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

okay. Will try that in excel. Thank you