Populating All combinations of 4 columns

[PANZON]

Is there any way to do this in Crystal? I am picturing a cross tab report.

* We have 4 columns for 4 years - current year and the last 3 years.

* Want to summarize the columns with total sales in those years.

* The rows will have a count of the customers that have purchased product in a specific sequence.

For example, want to see how many customers bought product in all 4 years. Want to see how many customers bought product in first 3 years but not this year. Want to see how many customers bought product in first 2 years, skipped a year and then bought again the last year. Etc. I came up with 15 combinations of these scenarios.

I can't figure out any way to appropriately group these so

 

[billie]

you can use date range function to do it.
Like suppose date is in Order Date field
If {orders.ORDER DATE} in LastYearYTD Then
{orders.ORDER AMOUNT}
Else
0
IF this clue works then fine else let me know

 

[kenhamady]

Sounds like the result would be 15 individual numbers, one for each scenario telling how many people met that scenario.

I don't see a use for the cross-tab.

I assume that you are working with detail records that each have a date and customer ID, so you have to evaluate a group of records for each customer to see what scenario they fall into? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[PANZON]

Ken hit the correct chord with what I am trying to accomplish here. Basically dropping a count of customers into one of the 15 buckets and then displaying their yearly purchases. Will be using Order Details for this mainly although will be joining a couple other tables.

 

[kenhamady]

Here is an outline:

First calculate the 4 years in 4 formulas.
Then create 4 more formula fields that look like this, one for each year:

if Year({date}) = {@Year1} then 1 else 0

Group by Customer and in each group calculate the Maximum of each of the 4 if-then formulas.

Now create 15 running totals using:
Insert - Running Total
Field = Cust
Operation = Distinct Count
Evaluate will be one of 15 formulas like:

Maximum({@count1} , {Cust}) = 1 and
Maximum({@count2} , {Cust}) = 1 and
Maximum({@count3} , {Cust}) = 0 and
Maximum({@count4} , {Cust}) = 1

Reset = never.

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal