Insert Group based on Running Total or Sum

[byrne1]

I am looking at my customer sales history for the past 12 months based on the SALES table. I want to group my customers as follows based on sales volume over the last 12 months and show how many customers I have in each group:

$10,000+
$5000 - $9999
$2500 - 4999
1000 - 2499
0 - 999

SALES table
<SaleTTL>
<CustNum>

CUSTOMER table
<CustNum>
<Name>

I can group the customers based on CUSTNUM just fine and see their total sales. My problem is that I don't know how to group them into the aformentioned sales volume categories.

 

[lbass]

What is in the salesTTL field? It is unclear whether you already have a summary available in the form of a database record ({table.salesTTL} or whether you have to insert a summary to get the 12-month total. You also don't show a date field.

-LB

 

[byrne1]

Yeah, I just showed the fields that I am really working with. the SaleTTL field represents the total amount of the individual sale (there is a SaleDate field associated with each sale but I'm not really that concerned with this field). I created a summary of this field at the CustNum group level to show the total sales for a particular customer for the previous 12 months.

I hope that answers your questions.

 

[lbass]

How can you not be concerned with the salesdate field if you are using it to determine the 12 month period? Please clarify how you are using the date in the report. Also, by previous 12 months what exactly do you mean? 12 months before today? last 12 full months? last full calendar year?

-LB

 

[byrne1]

Oh, yeah, I am using that field to determine my last 12 months. I said that I wasn't concerned with that field because I didn't see how it would help me in grouping the customers according to their total sales volume (sure, it's important in creating the dataset). I am looking at sales over the last 12 full months, from 4/26/06 to today.

 

[lbass]

In order to do this, try creating a command like:

Select (
select sum(A.`salesTTL`) from Sales A where
A.`CustNum` = Sales.`CustNum` and
A.`Date` >= {fn now()}-365 and
A.`Date` <= {fn now()}
) as sumamt, Sales.`CustNum`, Customer.`Name`
From Sales Inner Join Customer on
Sales.`CustNum` = Customer.`CustNum`

Ideally you would use the equivalent of dateadd('yyyy',-1,currentdate) instead of {fn now()}-365, but I'm not sure how to do it in SQL, and how you do it varies with your datasource, as does the punctuation and syntax. But you need to use a command or the equivalent to accomplish what you want to do.

Then create a formula like:

if {command.sumamt} < 1000 then "0 to 999" else
if {command.sumamt} < 2500 then "1000 to 2499" else
if {command.sumamt} < 5000 then "2500 to 4999" else
if {command.sumamt} < 10000 then "5000 to 9999" else
"10000+"

Insert a group on this and then insert a count on {Customer.CustNum}.

-LB