4 Replies Latest reply: Apr 25, 2013 3:08 AM by Bart De Bie RSS

    Set analysis range

      Dear all,

       

      I have a question regarding the use of date ranges in set analysis.

       

      When using the following syntax I'm getting 4 results as return

      SerialDatePrice
      A01/01/201310000
      B16/12/20129000
      C10/10/20129500
      D30/10/20128000

       

      sum({$<MonthYearNum ={"<= $(=Max(MonthYearNum))"},MachineStatus = {'Consigment','Demonstration','Free', 'Ordered', 'Purchasing', 'Reserved'}

      ,Year=,Month=,Quarter= >}MachinePurchaseCost)

       

      MonthYearNum will return 201301 as a number.

       

      What I wanted to achieve is to have a date range for example every serial which has a date between the month/year I've selected. (eg: Jan/2013). When I choose this, I'm counting back 2 months. So I will get All serials between November 2012 and January 2013.

       

      This is giving me 201211: Year(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)) & Num(Month(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)),'00')

       

      However, when I integrate these two syntaxes I'm getting nothing as result. My end result should be Serial A and B.

      sum({$<MonthYearNum ={"<= $(=Max(MonthYearNum)) >= Year(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)) & Num(Month(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)),'00')"},MachineStatus = {'Consigment','Demonstration','Free', 'Ordered', 'Purchasing', 'Reserved'}

      ,Year=,Month=,Quarter= >}MachinePurchaseCost)

       

      Am I converting something wrong?

       

      I appreciate the help.

       

      Kind regards

        • Re: Set analysis range

          I'm not entirely sure why that expression isn't working - maybe it's because the & converts things into a string, which isn't being read as a number? If so you could try 100*year + Month and see if that helped.

           

          However, it might be easier to go for the expression:

           

          Date={">=$(=num(addmonths(vMonthYear, -2))) <=$(vMonthSelected)"}, I.E. Why use MonthYearNum if you don't have to?

            • Re: Set analysis range

              I've been using MonthYearNum quite a bit. But it's indeed easier to use the expression you said. Both expressions give me the correct results.

               

              Thanks for the hint though. This makes my expression shorter and easier to understand.

            • Re: Set analysis range
              Fernando Suzuki

              I think you should've included the second part inside a dollar-sign expression:

              sum({$<MonthYearNum ={"<= $(=Max(MonthYearNum)) >= $(=Year(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)) & Num(Month(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)),'00'))"},MachineStatus = {'Consigment','Demonstration','Free', 'Ordered', 'Purchasing', 'Reserved'}

              ,Year=,Month=,Quarter= >}MachinePurchaseCost)