Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Many thanks in advance.
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))