Last sales order by ship to

[crystalize]

I am trying to create a report that will list the date of the last order placed by a customer, by ship to address.

I have 3 columns: Customer Key, Ship To Key, and Ship Date.

Sometimes the ship to key will be blank, meaning we need to default to the Customer Key because the ship to is the same as the Customer Key.

Many times there will be multiple entries for each Customer key and for each Ship To Key. I only want to show the last ship to date.

I am thinking it should go something like this:
If there is a cust key and a ship to key use the ship to key, if there is not a ship to key use the cust key.

Somehow, I need to select only the latest date for each of these.

 

[lbass]

If I'm understanding you correctly, create a formula like this {@grpkey}:

if isnull({table.shiptokey}) then
{table.customerkey} else
{table.shiptokey}

Then insert a group on {@grpkey}, and go to report->selection formula->GROUP and enter:

{table.shipdate} = maximum({table.shipdate},{@grpkey})

This would give you the most recent date per shiptokey (if it exists), or per customer if it doesn't.

Note that your attachment is on YOUR c:// drive and is unavailable to us. You could just show a mockup sample of your records to explain the issue if the above is not what you intend.

-LB

 

[crystalize]

if isnull({table.shiptokey}) then
{table.customerkey} else
{table.shiptokey}

When I create a formula for this I get "a statement is expected here" and it highlights {table.customerkey}

Is there something else I need to do?

 

[lbass]

In the formula expert, above the formula area, change the syntax to Crystal syntax (instead of Basic).

-LB

 

[crystalize]

This is working well. The situation changed some from when I originally posted here. I found that in the data there are no blank ship to's they default automatically from the customer key if nothing was entered.


I had to suppress duplicates. I created a formula called ship dates: formula = Maximum ({ARHDRH.Shipdate},{@ship to name}).

Here is the ship to name formula formula = {ARHDRH.Shiptoname}

The question I have is how to create a parameter to select certain dates like from 1/1/03 to 12/31/03. When I create this I get any sales within this time frame instead of the last sale for this customer.

 

[lbass]

If you use a daterange parameter to limit the pool of records to a certain range, you can then display only the most recent record within that range by going to report->selection formula->GROUP and enter:

{ARHDRH.Shipdate} = Maximum ({ARHDRH.Shipdate},{@ship to name})

You would then need to use running totals for any calculations across groups, since non-group selected records would contribute to the more usual inserted summaries. Note that you need no suppression if you use group selection.

-LB

 

[crystalize]

How do I create runnng totals across groups? I am only familiar with creating running totals within groups.

When I select 2003 to 2007 the running total shows the correct last ship date. When I select 2003 it show the last ship date being in 2003.

 

[lbass]

A running total at the report level would have Reset: Never.

The record selection formula creates your pool of records, and the group selection formula then further limits the records within that pool, so if you select dates in 2003, those are the ONLY dates available in the report, and therefore the group selection will be based on the most recent date in 2003. If you want the most recent date EVER, you should just limit records with a start date, no end date.

-LB

 

[crystalize]

Thanks for explaining the difference between record selection and group selection. I found that helpful. I am begining to understand it, but I think that is where my problem is.

I have as record selection: {ARHDRH.Shipdate} = {?Date Range}

I have group selection:

{ARHDRH.Shipdate} = Maximum ({ARHDRH.Shipdate},{@ship to name})

The problem I am still having is customer ADV01, their last ship date is 8/20/07 but when in the date parameter I choose 2003, it shows me the last ship date in 2003.

I tried your idea of no end date, which worked well, except for the huge amount of records I would need to go thru to see just the ones in 2003.

I even tried switching the record and group selection choices but I got an error message saying I couldn't save the group selection because this function can't be used because (?range) needed to be evaluated later

 

[crystalize]

LB, Thanks for your help. I think I am okay for now.