1 Reply Latest reply: Jan 6, 2011 6:30 PM by John Witherspoon

# Set analysis: last week of the previous year

Hello,

hopefully anyone can help me.

The following funktions works for the complete year except for the first week.

SUM({\$<Calendar.Week={\$(=Only(Calendar.Week)-1)}>} revenue)

How can I calculate the revenue for the last week of the previous year if the first week is chosen.

• ###### Set analysis: last week of the previous year

If you're comparing this week to the previous week, I might move the logic to the script to tie each week to each previous week.

AsOf:
AsOfYear, AsOfWeek, WeekType, Calendar.Year, Calendar.Week
2011, 2, Current, 2011, 2
2011, 2, Previous, 2011, 1
2011, 1, Current, 2011, 1
2011, 1, Previous, 2010, 52
etc.

pivot table:
Dimension 1 = AsOfYear // left
Dimension 2 = AsOfWeek // left
Dimension 3 = WeekType // top
Expression = sum(revenue) // no set analysis required

If I didn't want a solution that complicated, I might use a WeekDate field defined as the first date in the week, then subtract 7 days from it with set analysis.

sum({<Calendar.WeekDate={\$(=date(only(Calendar.WeekDate)-7))}>} revenue)

But you're probably looking for something like this:

if(only(Calendar.Week)>1
,sum({<Calendar.Week={\$(=only(Calendar.Week)-1)}>} revenue)
,sum({<Calendar.Year={\$(=only(Calendar.Year)-1)}
,Calendar.Week={\$(=max({<Calendar.Year={\$(=only(Calendar.Year)-1)}>} Calendar.Week)-1} revenue))