5 Replies Latest reply on May 25, 2012 1:13 PM by Tucker

    Month Number

    Tucker

      Title

      Month Number

      Post

       I have a typical date field in a table. I have added a field of calculation type in the same table, to represent the month number of each seperate date from the date field that uses the calculation: Month(DateFieldDate). However the results do not show diffrent values for each of the different months in the date field. They all show up with the same value instead (4). I cannot understand why this is?

        • 1. Re: Month Number
          philmodjunk

          Is this calculation in a calculation field that returns a number or is it a number field with an auto-entered calcualtion?

          I'm guessing the latter here...

          • 2. Re: Month Number
            Tucker

            Hi Phil. The calculation is in a calculation type field that returns a number. The date field that the calculation looks at is just a standard date field.  

            • 3. Re: Month Number
              Tucker

               Let me back up a bit, as I may not be on the correct path.

              What I'm trying to accomplish is to get a sum of order values for a number of customers by each month. I could do this by creating 12 table occurances of the order table and define the month as a filter but I'm trying to avoid that if possible. For each order, the Order table has an order date and order value (and other items related to orders). Are table occurances the only way?

              • 4. Re: Month Number
                philmodjunk

                If your date field is of type date and you are using a calculation field, you should see the number 4 returned only if the date entered into the field is from the month of April. So I can't tell from here why you kept getting the same result every time for different dates.

                I usually use this calculation when I need to group records by month:

                DateField - Day ( dateField ) + 1

                and use date as my return type. This produces date for the first day of the month for the same month as recorded in the date field. This gives me a single value that I can use to group my records by month and year.

                I can then define a summary field that computes the total of the Order Value field, putting it on a list view layout with a sub summary layout part with the above calculation field selected as its "when sorted by" field.

                If I put my summary field in the sub summary part, I get a monthly total.

                Such a report can list monthly totals and individual orders or just the monthly totals. The records must be sorted by the calculation field in order for the sub summary part to be visible. I can get monthly totals for different customers or groups of customers by performing finds to limit the orders to just the specified customer or customers.

                • 5. Re: Month Number
                  Tucker

                   Thanks! I will give it a try.