Variables

[#1]

Hi All

I have my dimension as [year week] and would like my objects or expressions to always give me 3 maximum year weeks.

I had created a variable called vmaxPeriod_3 calculated as max(YearWeek)-2 but sometimes when my max(YearWeek) is for example 201601, my vmaxPeriod_3 becomes 201599 when it should be 201511. How do l control this?

Please assist.

Regards,

Moses

[swuehl]

You could create another field in your model (assuming YearMonth):

     Year*12 + Month as YearMonthNum,

Then you can use this field to make your calculations across year bounderies.

Or create your YearMonth values as duals with underlying numeric date values and use AddMonths() to work out the YearMonth three months back.

[Gysbert_Wassenaar]

Create a field that can be used for such calculations. num(MakeDate(Year,Week)) as numYearWeek. Then you can use the Max function on that and use multiples of 7 to get earlier values: max(numYearWeek)-7 for the previous week.

[adinning]

You could also use the addmonths function.

Addmonths(YearMonth, -2)

This will however return the date as default, ie DD/MM/YYYY But should give the expected functionality.