creating dynamic groups from sales date

[ryan7]

I am currently trying to analyse a single table of data (example shown below). The table contains sales information with seller and customer ID's and we need to calculate when a seller sells more than 1 product to the same customer (at which point we assign a bonus 'sale' to the seller). To make matters more difficult, it only applies to sales done within 10 days of one another.

I've initially grouped sales by sellerID then customerID. I attempted to group the records by date as well, but it became apparent this wouldn't work (i..e. if two sales were made at the end of, and start of, two corresponding periods, they could be within 10 days of each other, but wouldn't be grouped together ).

What I would like to do is group the data using some sort of 'dynamic' group. So basically, the first group would be equal to the date of the first sale plus any sales in the 10 days following. The second group would consist of the next sale (outside of the first group), plus any sales made in the next 10 days. And so on.

Is something like this possible? Using crystal reports version 9.


Sample data:

Date, saleID, customerID
2006-01-01, 0014417, 38771454
2006-01-03, 0014417, 38771454
2006-01-14, 0014417, 38771454
2006-01-19, 0014417, 38771454
2006-01-23, 0014417, 38771454

Attempting to get an output along the lines of:

Date, saleID, customerID, running total
2006-01-01, 0014417, 38771454, 1
2006-01-03, 0014417, 38771454, 2
Group footer total: 2

2006-01-14, 0014417, 38771454, 1
2006-01-19, 0014417, 38771454, 2
2006-01-23, 0014417, 38771454, 3
Running total 3

 

[lbass]

I don't think you can do rolling groups like this, but if your goal is just to identify cases where the criteria are met, you can create a formula like:

if {table.customerID} = previous({table.customerID}) and
datediff("d",previous({table.date}),{table.date}) <= 10 then "Bonus"

You could then also insert a running total which does a count of {table.saleID}, evaluate using a formula:

{table.customerID} = previous({table.customerID}) and
datediff("d",previous({table.date}),{table.date}) <= 10

Reset on change of {table.seller}. Place the running total in the group footer for the seller group.

-LB