Weekly Cycle

[stevegimbrollmt]

Hi all

Not sure if there is any interesting method in achieving something like this:

	16/09/2013	23/09/2013	30/09/2013	07/10/2013	14/10/2013	21/10/2013	28/10/2013
Call Answered	1234	2342	2342	234	23423	15127	16357
Calls Rejected	4324	2342	2432	23423	234	19269	21508
Emails	423423	2342	242	2342	2342	5713	3773
Web Views	4234	2432	2342	2342	234234	130507	138090

Where I would have a daily record but I need to represent the figures on weekly cycle starting on Mondays for the past 7 weeks.

Any ideas? Do I need to use the Ceil function?

Thanks

Steve

[israrkhan]

create a week field in load script,

like below

load * ,

week(Date) as Week Num

from .......

and then take pivot table, add [Call Answered Field] to your first dimension and week-number to your second dimension,

and then select and drag up  week number dimension to horizontal.

[Gysbert_Wassenaar]

You want to show only the values of the mondays? In that case you could create a flag field in the script for the mondays:

Load x,y,z,

MyDate,

if(weekday(MyDate)=1,1,0) as IsMonday

from ...

You can then adapt your expression using the new IsMonday field. Count(Value) would become count({<IsMonday={1}>}Value).

If you want to show weekly totals, but show the dates of the mondays you create a field StartOfWeek: weekstart(MyDate) as StartOfWeek. You can then use this field as dimension instead of MyDate.

[stevegimbrollmt]

That would be good for Mondays but as you asked,  the values I need them still to be weekly but sort of the date start of the week would be the Monday as I shown in the sample above.

Thanks a lot for your feedback

Steve

[stevegimbrollmt]

I have used  =WeekStart(Date) in the Calculated Dimension and it seems I got what I needed.

All I need to do now is do it for only the last 7 weeks.

Thanks..

Steve

[Gysbert_Wassenaar]

You can limited that in the expression. Something like this:

count({<Date={'>$(=max(Date)-49)<=$(=max(Date))'}>}Value)

Perhaps you'll want to use a weekstart in there too if your max Date isn't a monday.

[stevegimbrollmt · Accepted solution]

I did something simliar but I just created a Week field and worked on that.

Basically did thse 2 steps:

• =WeekStart(Date) in the Calc Dimension
• Sum({<WeekField={'<=$(=max(WeekField))>$(=max(WeekField)-6')>}Value) in the expression

Thanks again for your help