Last 7 Days Fri to Thur

[rustyfishbones]

I want to show this field as a header in a table…

…the previous weeks dates, but this week doesn’t change until Friday of each week so for the whole of this week it’s for instance the 24^th of May to the 30^th of May, then on Friday the 7^th of June that date changes to the 31^st of May to the 6^th of June. I want the date to change as the weeks progress, not manually change the heading every week.

I’m using:

='+1 Weeks Sales '&date(today()-7,'DD/MM/YYYY')& '-' &date(today()-13,'DD/MM/YYYY')

which works fine for a rolling week but I want the week to remain fixed at ‘last Thursday to the previous week Friday’.

I then have to progress this formula to +2 weeks, +3 weeks….. always Thursday to Friday.

Any Help is appreciated

I have attached a sample Excel file of what I need

[marcus_sommer]

You could generate a separate week-field in script - in fact-table or master-calendar:

if(weekday(date) >= 4, week(date) + 1, week(date)) as Week_Fr_to_Th

Another way is a matching like years to fiscal years - example should be here in this forum.

- Marcus

[rustyfishbones]

It's close but not quite what I am looking for

I have added it in the script and it is doing something, but I need to check why it's not doing what I need

Thanks

[rustyfishbones]

The below Expression is Working for me, but how to get the same time period for the previous week

Friday to Thursday

the below gives me from 31/05/2013 to 06/06/2013

Next I need 24/05/2013 to 30/05/2013

[marcus_sommer]

The second parameter from weekstart/weekend function is a week shift, with him it should be possible to access previous weeks. I would prefer to create a special week field in script and use this as normal dimension ...

- Marcus

[rustyfishbones · Accepted solution]

Hi,

I got the answer here 

DUAL(PICK(WEEKDAY(Date) + 1, 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'), WeekDay(Date - 3)) AS WeekDay,

WEEK(Date-3) AS Week,

[#7]

Hi Alan,

I am also looking for the same calculation like to sum the values of last week from Friday to Thursday.

Could you please provide the steps to achieve this.

I stuck with the expression as well.

Thanks in advance for help.

[marcus_sommer]

If you have a week which differs from a calendar-week then it might be easier to consider this within a master-calendar like this one: Fiscal Calendar with Non-Standard Days (Not 1-31)

- Marcus