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...
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.
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?
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.
Thanks! I will give it a try.