Week vs. Previous Week

[danielle_v]

Hi All,

I'm putting together a report where I need to look at the selected week/year and be able to compare this to the previous week, the week before that etc.

At the moment, I have 2 calendar objects - one for year and one for week. The user will select the year and the week in order to view the results.

This works fine, apart from over the start of a new year. If I select week 1 2013 for example, I would want the previous week expression to show week 52 2012. However thsi does not seem to work.

I have tried creating a week-year filed in my script, but can';t seem to get anything to work.

Any help greatly appreciated!

[Gysbert_Wassenaar]

You can create a serial week field with this:

AutoNumber(weekyear(Date) &'|' & week(Date) as WeekSerial

[azimabadi]

can you please post set analysis for previous week?

[danielle_v]

Hi Gysbert - I have just tried this and I get numbers rather that week/years - for example week 10 2013 is showing in my list box as 394. Is it possible to show these as week/years in the list box?

Hi Peyman - The set analysis will be something like

ceil(sum({$<Cal_WeekYear = {Cal_WeekYear - 1}>} SalesValue))

So if I selected week 10 2013 in my calendar, I would want the above expression to show the sum of SalesValue for week 9 2013. (if I had selected week 1 2013 in my calendar, I would want the expression for previous week to show the sum of SalesValue for week 52 2012).

Thanks!

[Gysbert_Wassenaar · Accepted solution]

Danielle,

The idea is to use the serial weeknumber field in the set analysis expressions and week and year fields for making the selections. sum({<Cal_WeekYear=, Cal_Year=, WeekSerial={$(=max(WeekSerial)-1)}>} SalesValue) will return the right result when you select week 1 in 2013, i.e. the sales of the last week of 2012.

[azimabadi]

how looks Cal_WeekYear like? is it something like "201301" or "2013-01" for first week of 2013?

[danielle_v]

This works perfectly! Many thanks Gysbert!