Getting totals for the same time period each year, or each month, or each week...

[mikeinfbg]

I have a data table called SALES. Fields include: Date; DateTime; InvoiceNum; Price.

I am trying to write a formula (or set of formulas once I get the basic concept) that can do a few things and I am having great difficulty. All 3 of the following questions below boil down to me not knowing how to pick a "particular date or time" over a "time period who's length contains the particular date but also who's time period is cyclical". I think once I can narrow filter down the data set, I can do all the averageing, sorting, grouping etc.. on my own.

1) What I'm looking for is a way to group and total sales over many years by a Day of the Week, for example.

or

2) On avergage, what are our busiest times during a single day over a "X" amount of time (weeks,months,etc..)

and finally

3) What are our sales for a certain DATE, March 20th, for example, over many years.

Thanks for your help.

Mike

 

[pmax9999]

To Group/Average by Day of week, group by this formula:

WeekdayName(Weekday({Table.DateField}))

To Group/Average by actual Date (ignoring years), group by this formula:

ToText({Table.DateField}, 'MMM - dd')

Pete

 

[mikeinfbg]

I'm having difficulting on the "Selection" part of the process. As an example: I want to select all invoices that occurred on the Date July 1 but over several years, say 2008-2013. I can then compare sales based on those records for July 1st of each of those years.

 

[pmax9999]

Based on your original post, I had assumed you would simply select all of the data over a given period (driven by a parameter date range), and then analyse that data across the 3 different measures you mentioned. The easiest way would then be to use Cross Tabs which allow you to analyse and display the same data set many different ways, with the use of different groups.

However if you just want to use record selection to return data for a single date across numerous years, there are numerous approaches, such as:

1. Hard-coded dates - approach 1

{Table.DateField} in [Date(2008,7,1), Date(2009,7,1), Date(2010,7,1), Date(2011,7,1), Date(2012,7,1), Date(2013,7,1)]

or

2. Hard-coded dates - approach 2

{Table.DateField} > Date(2008,7,1) and
Month({Table.DateField}) = 7 and
Day({Table.DateField}) = 1

or

3. Using numeric parameters of {?YearRangeStart} and {?YearRangeEnd} allowing the user to select the year range

Year({Table.DateField}) in [{?YearRangeStart} to {?YearRangeEnd}]
Month({Table.DateField}) = 7 and
Day({Table.DateField}) = 1

I could go on. The first thing you really need to do is scope the report in terms what you want to achieve and whether by a single report (using Cross Tabs or perhaps sub-reports) or you would prefer multiple reports addressing a single point of analysis.

Hope this helps.

Pete