Creating a date range calculation

[Rikoshadow]

I'm trying to create a summary of invoice totals per week buy can't get it to work. Can anyone help me with the correct formula? All help is greatly appreciated.

Thanks,

Rick

[CobaltSky]

It's a little difficult to interpret your question. However if by "per week" you mean that you would like to see a total of the invoices for the current week (ie starting "last Sunday" and ending "next Saturday"), then here's one way you might approach it:

1. Create an unstored calculation field called cLastWeek, select result type date and enter the formula:

Let(T = Get(CurrentDate); T - DayOfWeek(T))

2. Create another unstored calculation field and call it cNextWeek, select result type date and enter the formula:

Let(T = Get(CurrentDate); T - DayOfWeek(T)+ 8)

3. Create a relationship from the current table to a new table occurrence of your invoice table (call the new table occurrence ThisWeeksInvoices) and define the relationship as:

cLastWeek < InvoiceDate

AND

cNextWeek > InvoiceDate

4. Create an unstored calculation in the current table called cCurrentWeekTotal, set the result type to number and enter a formula along the lines of:

Sum(ThisWeeksInvoices::InvoiceTotal)

...where "InvoiceDate" and "InvoiceTotal" are the names of the date and total fields in your invoice table.

The cCurrentWeekTotal calc (defined at step 4) should provide you with a current total for invoices dated within the current week. :D

[Rikoshadow]

Thanks so much. I was able to make it work the way you instructed me to. It's exactly what I was looking for. I also want to create a "From" and "To" fields where I can enter a date range for example; "From: 1/1/2007" "To: 1/31/2007" and have a summarry field containing "Invoice Totals" for the generated results.

[Rikoshadow]

Thanks so much. I was able to make it work the way you instructed me to. It's exactly what I was looking for. I also want to create a "From" and "To" fields where I can enter a date range for example; "From: 1/1/2007" "To: 1/31/2007" and have a summarry field containing "Invoice Totals" for the generated results.