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

    Determine Fiscal Month and Date Range of Report

    Duras _

      XYZ COMPANY    PAGE:    1

      DATE: 2/13/05 SALES REPORT          

                                        

                               

      ITEM NO DESCRIPTION   FISCAL MNTH 2/5 

      1234567     Deluxe Widget   200

      7654321     Economy Widget     500

       

      Hope above is formatted well enough to give an idea.

       

      What I am trying to accomplish is taking the date a report was run (from Header) along with a month range expressed as end month/start month to determine the date range of the data within the report.  

       

      Background

       

      Our fiscal month ending dates are always the last Saturday of the calendar month.  The current month is always month 1 and the previous month 2, 3, etc. with a maximum range of 24.

       

      Breaking down above example:

       

      Date report run: 2/13/05

      Month range of report: 2/5

       

      Since current date is 2/13/05 and is the current month (1) then Month 2 would be January and the last Saturday in January is 1/29/05. To get month 5 you would

       

      count backwards with Month 1 being February then 2 January, 3 December '04, 4 November '04 and 5 being October '04. Now since I want to find the first day of

       

      October's fiscal month I would determine September's last Saturday was 9/25/04 making the 1st day of October 9/26/04. 

       

      The final answer for Month 2/5 would be a date range of 9/26/04 through 1/29/05.

       

      I would like to stay away from tables for portability (read copy & paste) reasons and would prefer a formula I can reuse over and over since many of the reports I process with Monarch have the same attributes.

       

      I've been able to solve most programming situations I've come across but this one has me stumped.  Any assistance in solving this brain teaser would be greatly appreciated.    

       

      Thanks,

      Shannon

        • Determine Fiscal Month and Date Range of Report
          Grant Perkins

          Shannon,

           

          Nice challenge.

           

          Which version of Monarch do you have access to and is it Pro or Standard?

           

          Regarding Lookup tables - do you wish to avoid them because cut and paste on the internal lookup tables is perhaps a little trickier than cut and paste of a formula?

           

          If you have the Pro version have you looked at the possibility of maintaining an external file or data table that maintains the 'corporate month' start and end dates and doing a lookup/compare against that? Since the dates are fixed by the rules you outlined it seems to be that doing the calculation once and storing might be a better option than repetitive calculation. However that is just my thinking.  smile.gif[/img]

           

           

          Grant

           

           

          Originally posted by Duras:

          [font="courier"]XYZ COMPANY    PAGE:    1

          DATE: 2/13/05 SALES REPORT          

                                            

                                   

          ITEM NO DESCRIPTION   FISCAL MNTH 2/5 

          1234567     Deluxe Widget   200

          7654321     Economy Widget     500[/font][/quote][/b][/quote]

          • Determine Fiscal Month and Date Range of Report
            Duras _

            Grant,

             

            I'm using verion 7.02 pro.  Never thought about using a external lookup link and while I partly understand what your suggesting I'm not really sure how to accomplish it. 

             

            I guess I was sure there was some elaborate formula that could calculate what I wanted but it made my head hurt trying to think one up.

             

            If a formula can't be conceived (at least without too much trouble) then any guidence on using the external lookup would be appreciated.  I currently have several models using links external to Excel files on our server so I have that going for me.

             

            Shannon

            • Determine Fiscal Month and Date Range of Report
              Grant Perkins

              Originally posted by Duras:

              Grant,

               

              I'm using verion 7.02 pro.   /b[/quote]Excellent. Maximum number of ways to approach the problem. I will have a play and see what looks good - but I may find that some of the others on the forum get in with their solutions ahead of me!

               

              I know there is one in the files for finding the last day of a month and doing things with it but I can't remember one that looks for the last Saturday. (Or anything by day of week).

               

              Grant

              • Determine Fiscal Month and Date Range of Report
                Grant Perkins

                Shannon,

                 

                The more I think about this the more I suspect I am missing something.

                 

                I have read your first detailed post and that makes sense - EXCEPT nowhere in you sample data are lines with relating dates that would bneed to be calculated.

                 

                If the detail is based on the "2/5" meaning "the next block of data is month 2 and the report needs to go back 5 months, then all we need to know is whether the month 1 date represents, in this case, February or March. Clearly it would be February since the last Saturday in a month can only occur in the last 7 days of the month.

                 

                Now I have a formula to ascertain whether the report date - 2/13/05 in this case - is in fact the last saturday or is related to the month part of the date or the subsequent month.

                 

                And I was then looking at the most appropriate way to cover the rest of the output - 2/5, 3/5, 4/5 and 5/5.

                 

                But this may not be quite how things are - so I though I had better check with you first.

                 

                Have I got the interpretation right or wrong?

                 

                Grant

                • Determine Fiscal Month and Date Range of Report
                  Duras _

                  Grant,

                   

                  First off thanks for taking the time to work on my little puzzle.

                   

                  I do think I glossed over a few points and will try to make amends by hopefully explaining myself a little better.  So here goes.

                   

                  The actual (system) date the report was run (printed in the header) is always within month 1.  This is why reports almost always are run for period ranges not including the current month (month 1) since most users don't want to include an incomplete month.

                   

                  In posting my sample report I had trouble getting the columns to line up properly and may have made my example a little less clear than it should have been.  Just like Itemno & Description "Fiscal month 2/5" is a column heading and represents a period range for the data below it.  In this case the quantity in the detail line 200 & 500 should have been below the column heading of 2/5.

                   

                  One more thing about the "Fiscal month 2/5" column heading it could be for any range of periods.  From a beginning month of 1 to an ending month of 24.  The first number before the slash is always lower or equal to the 2nd number after the slash.  This means if someone wanted the report only for the previous month the column would read "Fiscal month 2/2".

                   

                  Oh, and the reason I preferred a formula is that I like to keep a text file with my most common formulas handy making it easy to paste into a new model.  I find tables within a model a real pain to enter if you have more than a few items since they definitely were not setup for real data entry.  You do bring up a good point about an external table since they are easy to create in excel and can be used for many models without the need to recreate them for each model.

                   

                  I hope I made myself a bit clearer and didn't make things seem too hard.  Thanks again for your ideas.

                   

                  Shannon

                  • Determine Fiscal Month and Date Range of Report
                    Grant Perkins

                    Hi Shannon,

                     

                    I take your point about the text file with the formulae. A good idea when there are things that you re-use regularly.

                     

                    I use the same idea for internal lookup tables. Seems to work best if you have the possible values and their interpretation in 2 columns tab separated. If you add the table and go to add the first record you can cut the 'master' data in 2 columns from your source document and paste directly into the lookup table from the first cell (left top) and it will fill the rest of the table in the two columns.

                     

                    Back to your problem.

                     

                    The date on the header gives us Month 1 - current month. We need to check to see if the date is before or after the last Saturday of the MONTH in the Date. ONce we know that we know that month 1, in this case is February and therefore month 2 will be January, 3 will be December and so on.

                     

                    The 2/5 column (Or 3/16 or whatever) is reporting values for transaction which have already been assigned to a monthly period by the report. The 'bucket' into which they have been dropped is also dependent on the fiscal month from the report date.

                     

                    As there is no date against each transaction (in the sample at least!) we can deduce that the 2 of 2/5 in this case relates to the January fiscal month as already determined by the report but not clearly reported

                     

                    So the way I see it is that there are 2 parts to the problem.

                     

                    Part 1 is to decide which fiscal month period the report falls into which we do from the header date.

                     

                    Part 2 is to then assign a month name to the value "2", "3", "4", thru "24" a reverse sequence of number to month/year interpretation from the now identified month "1" fiscal period.

                     

                    The second part of part 2 is defining how many periods to report but I am not sure that is important for your purpose. If we expect up to 24 periods and plan accordingly any lesser number should be OK. If there is not data for some of the periods in the final output nothing will be reported for them.

                     

                    I have a method for part 1.

                     

                    I have yet to settle on an easy method for part 2 but also need confirmation that we can look at the problem as I have outlined. If I am on the wrong track I need to re-think it.

                     

                    Sorry to appear rather pedantic about this but from what I can see at the moment the second part is highly specific to a correct understanding and may take a while do make it optimal. I don't want to spend time going down the wrong path.     

                     

                    Let me know if I have it right or not. You can send me a private message if you feel it would be more appropriate.

                     

                    Have fun.

                     

                    Grant

                     

                    [size="1"][ March 04, 2005, 09:40 AM: Message edited by: Grant Perkins ][/size]

                    • Determine Fiscal Month and Date Range of Report
                      Grant Perkins

                      I now have a model that will do the analysis I described in the earlier post.

                       

                      In outline;

                       

                      It checks the report date by seeking the last Saturday in any given month.

                       

                      If the report date is on or before the last Sat, the current month number is month 1. If it after the last Saturday the next month number is used.

                       

                      Once we have the effective month I compare that to a fixed internal (could be external) lookup table, using month and year, that I use to get a month identifire number (I used Jan 2003 as month number 1 to give a range in excess of 24 months for this example. Jan 2005 is ID number 25.)

                       

                      For a report using 2/5 months as per the example I take the number 25 and subtract the relative month number (2) and add back 1 to get the reference number for that month (25-(2-1)=24) and use a reverse of the previous reference number table to lookup the related month and year.

                       

                      so if 26 is the number for 02/13/05, the 2/5 month will be 25 and 25 has a description of "Jan 2005".

                       

                      Using a summary I can display the "Jan 2005", "Dec 2004" months as "Across" fields and therefore have them create columns.

                       

                      Finally the sort order will be all over the place so I can either 'fix' the descriptions to give the order I require or use the descriptions as they are and use an internal sort order within the summary field properties to set up a display order for the values exhibited.

                       

                      There may be another way to do this which is more elegant but this one allows the concept to be annotated fairly clearly. It can also be set up quite quickly to be future proof for some time to come.

                       

                      I can supply both a model and my sample data file. Send me a PM with an email address and I will forward the samples ASAP.

                       

                      Best,

                       

                      Grant