10 Replies Latest reply: May 15, 2014 9:54 AM by Paul Wheeler RSS

    Filter by Month()

    Fred _

      I have a huge Cumulative Report by Expenditures that has something like 80,000 records.  And my boss wants me to filter the expenditures by month.  However the date format is 12Dec01, respecively with an allotment.  Anyone have any ideas how I could make a filter to grab this?  the month, the date functions are not giving me anything because they use set date formats.

       

      Any ideas would be greatly appreciated.  smile.gif[/img]

        • Filter by Month()
          Fred _

          By the way I would like my filter to look something like this

          Instr("PRL",[Document])>0 .And. Month(11Jun01) however if you think there is a better way thats great too.

          • Filter by Month()
            Grant Perkins

            Hi,

             

            Do you need to just filter or perhaps also have a sort option?

             

            One fairly quick way could be to split the 'date' you have to separate the MonthYear part, or maybe month and year separately, into calculated fields and then work with those. The Left and Right functions may be useful, depending upon the consistency of the data format (is it always 7 characters?)

             

            You could also have a sort field by month number simply by converting the abbreviated month description into a numeric value in another calculated field. Several ways to do that  - the best for your needs depends upon which version of Monarch you have. An internal lookup table would be easiest. Otherwise probably a nested IF statement?

             

            Others may have better suggestions but I hope this offers some inspiration. Your filter would then work simply by replacing the date component with the appropriate calculated field or indeed fields if you need to include year.

             

            Grant

             

            Originally posted by WRATH:

            I have a huge Cumulative Report by Expenditures that has something like 80,000 records.  And my boss wants me to filter the expenditures by month.  However the date format is 12Dec01, respecively with an allotment.  Anyone have any ideas how I could make a filter to grab this?  the month, the date functions are not giving me anything because they use set date formats.

             

            Any ideas would be greatly appreciated.   smile.gif[/img]  /b[/quote]

            • Filter by Month()
              Fred _

              no, Sort doesnt seem to do it right.  it ascends or descends but I still see they years mixed up. 

              It is alwasy consistent with the date in a 11June01 format.  I am gonna give your suggestion a shot.

              • Filter by Month()
                Fred _

                By the way...thanks for the help.

                • Filter by Month()
                  Winn _

                  Wrath,

                   

                      I just read your posting. Are you wanting just one month or mulitple months at a time? Also, are you filtering the same month(s) every time? I have a couple of different solutions based on your answers to these questions. Let me know.

                  • Filter by Month()
                    Fred _

                    Good question, I just got out of a meeting with the boss.  He needs me to filter a specific transaction called (PRL) short for payroll then create another filter grouped by the month.

                    • Filter by Month()
                      Grant Perkins

                      Looks to me like you already have your filter for PRL which you posted earlier.

                       

                      Is the Month filter really a filter or a sort and subtotal? Or possibly both a filter AND then sorted and subtotalled? Has your boss given you an indication of the format he/she would like to see the results presented? Does the year number come into this anywhere?

                       

                      (And which version of Monarch do you have?)     smile.gif[/img]  

                       

                      Grant

                       

                      Originally posted by WRATH:

                      Good question, I just got out of a meeting with the boss.  He needs me to filter a specific transaction called (PRL) short for payroll then create another filter grouped by the month. /b[/quote]

                      • Filter by Month()
                        Fred _

                        The version is 6. When I filter the PRL's my table has the transaction, date, amount persons ID.  My understanding was to filter them by month.  Now when I do a sort it does not do it by the proper month format. i.e January, Feb..so on.  The problems arises that to use the Month() function it has to follow the mm/dd/yy format in #'s.  My report has the format like 11June02, so i think I will try your suggestion of a look up table.  That might be the best bet i got to get this to my boss.  Thanks for your help  smile.gif[/img]

                        • Filter by Month()
                          Grant Perkins

                          Hi,

                           

                          If your pseudo date string is consistent e.g. ddMMMyy, you can easily make calculated fields for MONTH and YEAR (might be useful?)as follows.

                           

                          For year create a calculated field (as a character field may be best) using

                           

                          RIGHT(,2)

                           

                          This will also work if the date field is sometimes ddMMMMyy.

                           

                          To extract the MONTH try

                           

                          SUBSTR(,3,3)

                           

                          creating an alpha field.

                           

                          This will may work just as well if the format is  ddMMMMyy!! But you will need to check the results

                           

                          The new field with the Month Name is the one to use to create the final calculated field with the lookup values giving you months numbered 1 to 12.

                           

                          Now you can create a filter (or filters with various combinations) for your PRL need .AND. a month name (and presumably a year number to avoid getting JUN from several different years if the report covers several years.)

                           

                          Alternatively just use your PRL filter and SORT the results by (the year number field again for the reason just identified if it applies) and then the MONTH NUMBER. The month number could be a hidden field and you can simply display the Month Name that goes with the number. If your output is for several months you might want to consider using subtotals to separate the months for easier reading.

                           

                          I hope this helps. There are probably several other approaches to the problem but this is quite quick and simple to play around with and should work fine.

                           

                          Grant

                           

                           

                             Originally posted by WRATH:

                          The version is 6. When I filter the PRL's my table has the transaction, date, amount persons ID.  My understanding was to filter them by month.  Now when I do a sort it does not do it by the proper month format. i.e January, Feb..so on.  The problems arises that to use the Month() function it has to follow the mm/dd/yy format in #'s.  My report has the format like 11June02, so i think I will try your suggestion of a look up table.  That might be the best bet i got to get this to my boss.  Thanks for your help      smile.gif[/img]     /b[/quote]

                           

                          [size="1"][ April 30, 2003, 07:14 PM: Message edited by: Grant Perkins ][/size]

                          • Filter by Month()
                            Paul Wheeler

                            Originally posted by WRATH:

                            I have a huge Cumulative Report by Expenditures that has something like 80,000 records.  And my boss wants me to filter the expenditures by month.  However the date format is 12Dec01, respecively with an allotment.  Anyone have any ideas how I could make a filter to grab this?  the month, the date functions are not giving me anything because they use set date formats.

                             

                            Any ideas would be greatly appreciated.   smile.gif[/img]  /b[/quote]