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

    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.

      Many thanks in advance.

        • Set analysis: last week of the previous year
          John Witherspoon

          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))