1 Reply Latest reply: Feb 14, 2012 11:23 AM by Trevor Olney-Jones RSS

    Calendars, Year1+Week1 vs Year2+Week2

      Hi All,

       

      One of our main requirements is to be able to report a weeks worth of data from one year against a weeks worth of data from another year (not always the previous year so we need full flexibility, even the week numbers may be different).  For example:-

       

      2012.05 vs 2011.05

      2012.02 vs 2010.14

       

      I have created a master calendar which contains calendar date, year, month, week, day, year+week fields.

       

      The data table that contains sales date etc contains the calendar date field so that it is joined to the master calendar.

       

      No matter which i try to pull of year+week data (as in the examples above) I constantly get nothing returned.

       

      For example using the year and week fields seperately in the expression:-

      =Sum ({$<CALENDARYEAR={$(vYear1)},CALENDARWEEK={$(vWeek1)>}[SALES-EXTAX])-Sum ({$<CALENDARYEAR={$(vYear1)},CALENDARWEEK={$(vWeek1)>}[COSTOFGOODS])

       

      Another attempt has been using concatenated variables that contain YYYY.WW in their value:-

      =Sum ({$<CALENDARYEARWEEK={$(vYearWeek1)}>}[SALES-EXTAX])-Sum ({$<CALENDARYEARWEEK={$(vYearWeek1)}>}[COSTOFGOODS])

       

      Neither works and I have no idea why, and I'm starting to pull my hair out!

       

      Does anyone have any suggestions as to either what I'm doing wrong, or be able to point me in the right direction.

       

      Many Thanks

       

      Trevor