Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

rustyfishbones
Honored Contributor II

Last 7 Days Fri to Thur

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 24th of May to the 30th of May, then on Friday the 7th of June that date changes to the 31st of May to the 6th 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

Tags (3)
1 Solution

Accepted Solutions
rustyfishbones
Honored Contributor II

Re: Last 7 Days Fri to Thur

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 Replies

Re: Last 7 Days Fri to Thur

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
Honored Contributor II

Re: Last 7 Days Fri to Thur

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
Honored Contributor II

Re: Last 7 Days Fri to Thur

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

Last_7_Days_Friday_to_Thursday.png

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

Re: Last 7 Days Fri to Thur

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
Honored Contributor II

Re: Last 7 Days Fri to Thur

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,

Not applicable

Re: Last 7 Days Fri to Thur

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.

Re: Last 7 Days Fri to Thur

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

Community Browser