4 Replies Latest reply: May 15, 2014 9:55 AM by D Hart RSS

    evaluating a date field to return a period #

    D Hart

      I have a "Post Date" field in my database, and need to create a Period # field from it. For Fiscal Year 2004, period 1 = 1/1/04 through 1/30/04. Period 2 = 1/31/04 through 2/28/04, etc.

       

      I tried using If/Then, but get an Operand Type error. I've boiled the problem down to the first portion of my formula, which reads:

      "if(>='1/1/04',1,2)"

      I've simplified the formula for illustration purposes. Any suggestions, or better way to calc the period would be MUCH appreciated.

        • evaluating a date field to return a period #
          Data Kruncher

          I have a similar requirement. My company uses fiscal periods which don't coincide with calendar months, so just because the date on the report says October it may not pertain to the October fiscal period, it could be September or November, depending on the number of weeks in the October period. Anyway, back on track...

           

          My solution was to build an Excel sheet which lists every single day for a number of years in column A, and the fiscal period for that day in column B. Then build an external lookup in your Monarch model to find the date from the list, and retrieve the period.

           

          This will require a few minutes of your time to build the Excel file, but you won't need to build fancy formulas in Monarch. Someone else might have a way, but I couldn't build a formula to determine the right periods for my stuff in EVERY case, guaranteed. Fortunately, a quick external lookup makes this type of calculation relatively easy.

           

          Just make sure to list every day so that Monarch can make an exact match.

           

          Kruncher

          • evaluating a date field to return a period #
            Nigel Winton

            Guys

            I have a similar problem and resolved it by using Monarchs Week function to get a week number and then an internal lookup to find the period the week falls in. Depending on when you start your working year you may need to tweak the week function up or down one by using + or - 1 on the end of the formula to get the week number accurate. The beauty of this it is all internal to Monarch and therefore quicker.

            I did notice one thing with the original formula which would give the error you are getting, you have shown your date in ' marks when you should be using to enclose it.

             

             

            Regards

             

            Nigel

            • evaluating a date field to return a period #
              Dee Moore

              Hi,

               

              If your date periods are static, you should be able to create new calculated fields using the .AND. operator.

               

              And yes, as mentioned above, your short date needs to be surrounded by curly brackets: {}

               

              All Periods

              [font="courier"]If(>={1/1/04} .AND. (<={1/30/04},1,

              If(>={1/31/04} .AND. (<={2/28/04},2,0))[/font][/quote]Continue to "nest" IF() statements until you have covered all Periods.

               

              You have unlimited "nesting" in V8.

               

              Regards,

              Dee Moore

              Datawatch Tech Support

              • evaluating a date field to return a period #
                D Hart

                Thanks all for the tip about the brackets around the date. I setup a spreadsheet for the external lookup and it's all good!