3 Replies Latest reply: May 15, 2014 10:10 AM by elginreigner _ RSS

    Date Question

    Bradl Vogl

      I need to extract Month and Year from a date.

      I created a table called sales by year (with year running across) YEAR(DATE)

      I created a table called sales by month. MONTH(DATE)

      I have 10 years of data.

      The MONTH calculates all data falling in the month of ALL years.

      How can I calculate by YEAR/MONTH to have accurate totals for given month and year?

       

      Appreciate any help.

       

       

      Thanks,

      Brad Vogl

        • Date Question
          Olly Bond

          Hello Brad,

           

          You can do this in v10 using two across keys in a summary, or a new field (numeric) of Year()+(Month()/100) will give you values like 1999.04, 1999.05 etc.

           

          Best wishes,

           

          Olly

          • Date Question
            Data Kruncher

            Hi Brad,

            For this sort of periodic reporting I tend to create a date type calculated field. Since for this purpose we're really on interested in the month and the year, I force the day number to be the first of the month. So the field named Period which would appear as an across key in a summary would be built with:

            Ctod(Str(Month())"/1/"Str(Year()),"m/d/y")[/CODE]

             

            Then for the actual presentation of the Period values, if you'd prefer to see April-2011 instead of 4/1/2011, you can go to the Matching tab of the field definition for Period and select Time Interval, then Calendar Month.

             

            Further, you can use the Options menu, Time Intervals and then edit the formatting mask for Calendar Month if you want a difference appearance. You may need to recalculate the summary for your change to take effect.

              • Date Question
                elginreigner _

                Hi Brad,

                For this sort of periodic reporting I tend to create a date type calculated field. Since for this purpose we're really on interested in the month and the year, I force the day number to be the first of the month. So the field named Period which would appear as an across key in a summary would be built with:

                Ctod(Str(Month())"/1/"Str(Year()),"m/d/y")[/CODE]

                 

                Then for the actual presentation of the Period values, if you'd prefer to see April-2011 instead of 4/1/2011, you can go to the Matching tab of the field definition for Period and select Time Interval, then Calendar Month.

                 

                Further, you can use the Options menu, Time Intervals and then edit the formatting mask for Calendar Month if you want a difference appearance. You may need to recalculate the summary for your change to take effect.[/QUOTE]

                 

                +1, I use this same method to create the same groupping. Olly's option also works as well, he's just using a different date setup. Both will work perfectly fine.