Crystal Reports - Sum data by year on Crosstab

0
votes

I am using Crystal Reports 2013 to generate reports from our primary point of sale database. I am working on a report to show the average number of transactions each of our customers make in a given year, giving me a transaction frequency.

Crosstab

enter image description here

I can edit the Crosstab so that it displays totals for each year, but what I really need is for the total to ALWAYS include the prior 365 days. In other words, if I chose to display the crosstab data by month, the value for each month shown would actually be the sum of all values in the prior twelve months. This would be the case for each month.

What is the best way to go about this?

Thanks

1
sqlcrystal-reports
Are you providing any input parameter to view single month data? Or is there any other way you are following to pick the single month? – Siva
I'm not. The crosstab consists of Distinct Counts of both ticket numbers and customer numbers as summarized fields (with an embedded summary to give me the rate). The location (STR_ID) is the field used for rows. Date (TKT_DT) is used for columns, and Group Options>"The Column Will Be Printed For" is set for monthly on the screenshot. Ideally, the column printing option will be irrelevant, as the data will always include the trailing 365 days (displaying it monthly would include the distinct count of customer numbers and ticket numbers for 365 days beginning on the first of the month) @siva – Tyler Smelley

1 Answers

0
votes

Assuming your date parameter is Date value and you are taking current date into consideration while running the report.

Place the below formula in record selection formula and run the report.

{<<DateDatabasefield>>}>=DateSerial (year(currentdate)-1,month(currentdate) ,day(currentdate) )
and
{<<DateDatabasefield>>}<currentdate

This will make sure that data returned is for one entire year. If you run in june then it will pick data from 2013 june to 2014 june.

Let me know if you are looking for something different.