Report Studio: How to get last day of 13 Weeks

[RAHIOUI]

Hi ALL,
Help me plz :)
How to get last day of 13 Weeks
The last day of week is Sunday

[BigChris]

Hi, just need a bit of clarification first. Using today as an example (Tuesday December 17), what date do you want to return? Do you want the calculation to return 16th March or the 9th March?

Depending on what you want to return, you essentially work out what day of the week you're on now, then go forward or back the requisite number of days to get to the right Sunday, then go forward by 13 weeks.

C

[Lynn]

Also, the method will vary for a relational source vs. a dimensional source, so you should indicate what type of package you are working with in order to get the correct advice.

[RAHIOUI]

Here is the expected result for last day of 13 Weeks for a relational source :
- 22/12/2013
- 15/12/2013
- 08/12/2013
- 01/12/2013
- 24/11/2013
- 17/11/2013
- 10/11/2013
- 03/11/2013
- 27/10/2013
- 20/10/2013
- 13/10/2013
- 06/10/2013
- 29/09/2013

Thanks

[Lynn]

This expression will give you the upcoming Sunday from the current system date. Once you have that date, you can use the _add_days function to subtract 7 days to previous week, then subtract 14 days to get two weeks ago, etc.

In an ideal world you'd have a calendar dimension that would allow you to do this sort of thing much more simply and elegantly.

Code Select Expand


case _day_of_week( current_date, 1 )
when 1 then _add_days ( current_date, 6 )
when 2 then _add_days ( current_date, 5 )
when 3 then _add_days ( current_date, 4 )
when 4 then _add_days ( current_date, 3 )
when 5 then _add_days ( current_date, 2 )
when 6 then _add_days ( current_date, 1 )
when 7 then current_date
end


[RAHIOUI]

Thank you for your quick response :)
this CASE for have the last day of the week is very good
my problematic it is to this treatment for 13 weeks  :-\

[Lynn]

I'm not aware of anyone on the forum who is clairvoyant, although Paul, Blom and the muppet come pretty close  ;)

You'll need to work a little bit harder in providing information for us to go on.

I explained that you can take the expression I provided and use the _add_days function to subtract days and derive the earlier weeks. What part are you having trouble with?

What exactly are you trying to achieve in your report? Are you filtering or attempting to "bucket" your metrics weekly? If you explain further what you need to do and what, specifically you are having trouble with then you can get better help.

[MFGF]

I'm not aware of anyone on the forum who is clairvoyant, although Paul, Blom and the muppet come pretty close  ;)

Lynn. I have a mental vision of you sitting in an office cubicle with a large flat screen in front of you. The building you are in has a yellow sign. Somebody parked a car in the lobby. Your friends sit behind you and to the left of you. They are callesd Matt and Ben. Or something similar.

Oh - the vision is fading. I think that was my clairvoyance ration for thw whole of this year. Did any of it ring true? :)

MF.

[Lynn]

Lynn. I have a mental vision of you sitting in an office cubicle with a large flat screen in front of you. The building you are in has a yellow sign. Somebody parked a car in the lobby. Your friends sit behind you and to the left of you. They are callesd Matt and Ben. Or something similar.

Oh - the vision is fading. I think that was my clairvoyance ration for thw whole of this year. Did any of it ring true? :)

MF.

Like I said.....pretty close  ;)

[BigChris]

Ahhh...I think I can see what you're trying to achieve - it looks like you want to calculate the dates of the Sunday for the previous 13 weeks, where the end point is the Sunday of the current week. I haven't got Cognos on this machine, so this is from memory and you'll need to do some checking and tweaking to get it right...but I'll check it in the morning when I'm back in the office:

Sunday 22nd = cast(_add_days(getdate(),7-dayofweek(getdate())),date)
Sunday 15th = _add_days(cast(add_days(getdate(),7-dayofweek(get date())),date),-7)
Sunday 8th = _add_days(cast(add_days(getdate(),7-dayofweek(get date())),date),-14)
etc...

You get the picture anyway. You might need to tweak the calculations based on what system you're using, but that should give you a reasonable chance of getting the results you're after. And if those aren;t the results you're after then please be a bit more specific.

[navissar]

Perhaps a different approach is required here.
If I understand correctly, what we want to achieve is to have a list of 13 dates representing Sundays ending with the coming Sunday, starting with the Sunday precisely 13 weeks before that.
I think putting the question in these terms makes it easier to solve, and you don't have to calculate each date individually.

So first, we create a query with the date data item from the package.
Next, we create a calculated data item for day of week (Unless this already exist in data source/package) - I'll call it "Day of week":
_day_of_week ([Date],1) (This will return 1 for Monday, 2 for Tuesday...7 for Sunday).
Now, we only want to see Sundays, so we'll add a filter, [Day of Week]=7.
And since we want the thirteen weeks ending with the coming Sunday, we'll add another filter:

Code Select Expand

[Date] between
_add_days(_add_days(current_date,7-_day_of_week(current_date,1)),-84) and _add_days(current_date,7-_day_of_week(current_date,1))
I'll explain that last part:
_add_days(current_date,7-_day_of_week(current_date,1)) is the key here: it finds the coming Sunday. If today is Wednesday (3) then 7-3=4, and then we add 4 to current_date and we'll be on..Sunday! This expression can replace Lynn's case statement, although Lynn's statement is just as good.

So, I'm asking the query to filter [Date] when it's between the coming Sunday-84 days (7*12), and the coming Sunday. I already filtered to only see Sundays. Next stage: Profit!

Best of luck!

[RAHIOUI]

Thank you very much for your ingenious answers  :)
Lynn Excuse my English.
MFGF I smiled when I read your post.
BigChris, you understood my question is exactly to calculate the dates of the Sunday for the previous 13 weeks, where the end point is the Sunday of the current week. Thank you for your effort to solve my problem
Nimrod Avissar your answer saves my life this is exactly the solution I was looking for thank you very much for your answer,
thank you all