Split data out by Week in a Straight Table?

[#1]

Hello,

I have the following dataset:

Date

NumberofHits

Website URL

I'm trying to create a straight table that will have Website URL as the dimension, and then 4 Expressions, each of which will have NumberofHits per trailing week, so

Most Recent Week,

2 weeks old

3 Weeks old

4 weeks old

I need a straight table and not a pivot table, so I want to use Set analysis to split up the NumberofHits per Week.

I can't quite get the syntax right to do this.  Something like:

sum( {<Week (Date) = max (Week(Date)>} NumberofHits) ??

Thanks.

[jagan]

Hi Chris,

Try like this

For Most Recent week

=sum( {<DateDimensionName = {'>=$(=WeekStart(Max(DateDimensionName)))<=$(=Date(Max(DateDimensionName)))'}>} NumberofHits)

For 2 Weeks old

=sum( {<DateDimensionName = {'>=$(=WeekStart(Max(DateDimensionName), -1))<=$(=Date(Max(DateDimensionName)))'}>} NumberofHits)

For 3 Weeks old

=sum( {<DateDimensionName = {'>=$(=WeekStart(Max(DateDimensionName), -2))<=$(=Date(Max(DateDimensionName)))'}>} NumberofHits)

Note : Replace DateDimensionName with your actual Date field Name and make sure your date field and Today() should be in the same format.

Regards,

Jagan.