6 Replies Latest reply: May 15, 2014 9:57 AM by Grant Perkins RSS

    Tip: Calculating Fiscal Quarters

    Nick Osdale-Popa

      After posting my suggestion, I decided to do some searching and found a nice little routine to calculate the Fiscal Quarter for any date:

       

      We're still in transition to V9, so this is based on  V8 Pro:

      [font="courier"]MOD(CEILING(23MONTH()-[FiscalMonth]-1,3)/3,4)1[/font][/quote]I used as a runtime parameter and is the field that has your date to be calculated.

       

      This should be easily adaptable to a Custom Function in V9.

       

      Hope this helps someone!

      (I'm looking at you Data Kruncher  )

        • Tip: Calculating Fiscal Quarters
          Data Kruncher

          Much appreciated, Sir!    

           

          BTW, I was looking at a visual analysis tool recently which has a related cool feature: when you right click on a date field, one of the menu option is a Fiscal Year submenu. Hover over it, and a list of months pops up January to December. Click on the month name, say October, and now all of your analysis work "knows" that your fiscal year starts in October. When you want to view quarterly analysis, the program gets all of your quarters right based on your single choice. Nice.

          • Tip: Calculating Fiscal Quarters
            Grant Perkins

            Might be worth checking out the "Qtr_Advanced" User Defined Function in the UserDefinedFunctions.xmod model.

             

            V9 only as it stands since it makes use of the DateAdjust function.

             

              smile.gif[/img] 

             

             

            Grant

            • Tip: Calculating Fiscal Quarters
              Nick Osdale-Popa

              tongue.gif[/img]  Okay...Just to toot my own horn, compare that formula:

              [font="courier"]Qtr(if(month(q1_start)=2,DateAdjust(date,0,-1,0),

              if(month(q1_start)=3,DateAdjust(date,0,-2,0),

              if(month(q1_start)=4,DateAdjust(date,0,-3,0),

              if(month(q1_start)=5,DateAdjust(date,0,-4,0),

              if(month(q1_start)=6,DateAdjust(date,0,-5,0),

              if(month(q1_start)=7,DateAdjust(date,0,-6,0),

              if(month(q1_start)=8,DateAdjust(date,0,-7,0),

              if(month(q1_start)=9,DateAdjust(date,0,-8,0),

              if(month(q1_start)=10,DateAdjust(date,0,-9,0),

              if(month(q1_start)=11,DateAdjust(date,0,-10,0),

              if(month(q1_start)=12,DateAdjust(date,0,-11,0),date))))))))))))[/font][/quote]To my formula!

               

              Plus, it took me a bit to figure out the second parameter, that it had to be in date format, whereas mine only needs to know the Month # to calculate.  

              • Tip: Calculating Fiscal Quarters
                Grant Perkins

                Beep Beep.

                [font="courier"]Qtr(DateAdjust(,0,-month()+1))[/font][/quote]Seems to do it (again for V9 ...)

                 

                The field "Fiscal Year start Date" would be a Runtime Parameter but if the value was stored it would effectively be permanent.

                 

                Seems to work but I have not tested extensively and there may be some anomalies.

                 

                Grant

                 

                E&OE

                 

                 

                PS. The other aspect of the UDF formula is that you don't need to worry about the formula  - just use the UDF as a function. (V9 only of course ...)

                 

                On the other hand Nick there is no reason why your formula (which I like a lot) should not also be a UDF available to everyone as a linked object ... better still it works for V8.

                 

                [size="1"][ April 11, 2007, 07:40 PM: Message edited by: Grant Perkins ][/size]

                • Tip: Calculating Fiscal Quarters
                  Nick Osdale-Popa

                  I was thinking about how the V9 formula could be further adjusted and you pretty much slammed it. However, the still has to be a date, rather than the numeric month. That formula has to be addressed as:

                  [font="courier"]Qtr_Advanced(MyDate,{10/01/2007})[/font][/quote]So to adjust it one more time it could be shortened to:

                   

                  [font="courier"]Qtr(DateAdjust(,0,-[Fiscal Year start Month]+1))[/font][/quote]And then addressed as:

                  [font="courier"]Qtr_Advanced(MyDate,10)[/font][/quote]Now both formulas are much more concise and doesn't require the user to type in a properly formatted date, ie is {10/1/2007} equal to Jan 10, or Oct 1? Which can definitely throw off the results. By using the numeric month value, there's no question about the results. Also an error check for the (limiting the values to 1-12) can be added to the formula.

                   

                  But as stated, this is all V9, my formula is a little more universal depending on the availability of those functions: When was Mod() and Ceiling() introduced into Monarch?

                   

                   

                  BTW: I think it's been mentioned before, but I wonder if a Forum for User Defined Functions can be created for members to post their formulas. They could get feedback on how to refine it just as we have done with this one.

                  • Tip: Calculating Fiscal Quarters
                    Grant Perkins

                    Nice work and good points Nick.

                     

                    Functions -

                     

                    Mod arrived with V6

                     

                    Ceiling (and Floor) with V7.

                     

                     

                    Grant