3 Replies Latest reply: Mar 22, 2016 8:37 AM by Andrew Dinning RSS

    Variables

    Moses Dowart

      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

        • Re: Variables
          Stefan Wühl

          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.

          • Re: Variables
            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.

            • Re: Variables
              Andrew Dinning

              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.