Chris,
As a way to measure customer retention / loyalty loss I ran a report to show customers who have ordered more than ten times since 2007, and not once in the last year. It uses ODBC and is not pretty as I used it for exporting to Excel for further analysis.
I will send to you offline.
Bruce B.
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Chris Thompson
Sent: Wednesday, September 01, 2010 5:28 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Report to show customers who havent ordered in x months
HI All,
I've tried your suggestions but I cant seem to get it to work.
I mean, it will work, but not how I want it to.
I want to group my results by month so I can see which month was the last month
customers bought so the report will have a summary page as follows ...
Bought this month
Bought Last Month
Bought 2 Months Ago
etc
I can get it working like that using the data (which I thought was correct) from
Epicor. But the 'last' filter wasnt working as expected.
The only way I can see how to do this is to sort the data correctly in Epicor
before exporting it.
DO you know how I can sort if properly in Epicor BAQ so it will export the last
order per customer and customers with no orders?
Thanks.
________________________________
From: Chris Thompson <chriselectrix@... <mailto:chriselectrix%40ymail.com> >
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Sent: Wed, 1 September, 2010 8:17:58
Subject: Re: [Vantage] Report to show customers who havent ordered in x months
Hi All
Thanks for your suggestions.
I am going to try the simpler ones first (mainly because words like
"concatenate" scare me)!
I will let you know how I get on.
Thanks,
CHRIS
________________________________
From: Roel Martinez <rmartinez@... <mailto:rmartinez%40sabre-sts.com> >
To: "vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> " <vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> >
Sent: Tue, 31 August, 2010 23:30:02
Subject: RE: [Vantage] Report to show customers who havent ordered in x months
I think Sean’s option is good and simple….
It works! Even the proper Order Number shows –depending on your Order Date
sorting direction (ascending or descending).
Great idea Sean…
Regards.
Roel Martinez
ERP Analyst
SABRE TUBULAR STRUCTURES â„¢
www.sabretubularstructures.com<http://www.sabretubularstructures.com/>
8653 E. Hwy 67 | Alvarado, TX 76009
Phone: 817-852-1700 ext. 11858 | Direct Line: 817-852-1858 |Fax: 817-852-1705
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On Behalf Of Sean
McDaniel
Sent: Tuesday, August 31, 2010 5:03 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Report to show customers who havent ordered in x months
Try this:
Create a new report joining Customer to OrderHed. (do this by ODBC or BAQ
Report)
Create a grouping by customer.
Add an order by OrderHed.StartDate descending.
In the customer group header put in the customer information and the
ordered.orderdate fields.
Suppress the detail section. Suppress the Customer Footer.
Add in a suppression formula on the customer header such that it suppresses
customers whose ordered.orderdate < 6 months ago
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>] On Behalf Of
Roel Martinez
Sent: Tuesday, August 31, 2010 4:43 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Report to show customers who havent ordered in x months
Hello Chris, all.
If you can afford bringing all your Customer’s Orders to Crystal, you could try
a little of a “dirty trick†to select the maximum Order Date, together with the
proper Order Number; if you concatenate the Order Date and the order number, you
can summarize by Customer, create a MAX Summary based on the concatenated field
(OrderDate + OrderNumber) and then extract your Order Number at the Group Header
level for your Customer, from the MAX concatenated field.
The concatenation formula would be something like: totext({orderhed.orderdt},
“yyyymmddâ€) + “-“ + totext({orderhed.ordernum}, “00000000â€)
In this formula, the “yyyymmdd†will ensure you can summarize (MAX) the date and
the “0000000†string will allow you to extract your order number with a RIGHT
function.
Don’t know if it’s more complicated than what you are looking for, but it worked
for me in a report. Contact me directly if you have any questions.
Regards.
Roel Martinez
ERP Analyst
SABRE TUBULAR STRUCTURES â„¢
www.sabretubularstructures.com<http://www.sabretubularstructures.com/>
8653 E. Hwy 67 | Alvarado, TX 76009
Phone: 817-852-1700 ext. 11858 | Direct Line: 817-852-1858 |Fax: 817-852-1705
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On Behalf Of Chris Thompson
Sent: Tuesday, August 31, 2010 10:41 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject - Email found in subject
Hi Sean
Sounds complicated.
In simple terms, do you know how to link the Customer table and the OrderHed
table to display all customers (so each customer has one entry in the results)
and only to show the last ordernum per customer if a customer has had an order
at all.
e.g.
raw results
cust1 order1
cust1 order2
cust2
cust3 order3
cust3 order4
cust3 order5
cust4
the results I want
cust1 order2
cust2
cust3 order5
cust4
Any thoughts would be appreciated. I only know enough to do things in BAQ
designer and Crystal - I dont want to start messing the ODBC or Progress.
Thanks in advance.
________________________________
From: Sean McDaniel
<smcdaniel@... <mailto:smcdaniel%40comtecsolutions.com> <mailto:smcdaniel%40comtecsolutions.com>
<mailto:smcdaniel%40comtecsolutions.com>
<mailto:smcdaniel%40comtecsolutions.com>>
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>;
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Sent: Tue, 31 August, 2010 16:31:22
Subject: RE: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject - Email found in subject
It's based on the index that is being used.
The OpenEdge documentation provided indicates:
FIRST
Uses the criteria in the record-phrase to find the first record in the table
that meets that
criteria. Progress finds the first record before any sorting.
FOR FIRST customer BY credit-limit:
DISPLAY customer.
END.
The procedure above displays customer 1 (cust-num is the primary index of the
customer
table), not the customer with the lowest credit-limit. A procedure that displays
the
customer with the lowest credit-limit looks like the following.
FOR EACH customer BY credit-limit:
DISPLAY customer.
LEAVE.
END.
LAST
Uses the criteria in the record-phrase to find the last record in the table that
meets that
criteria. Progress finds the last record before sorting.
The procedure above displays the customer with the highest customer number
(cust-num
is the primary index of the customer table), not the customer with the highest
credit-limit.
FOR FIRST customer BY credit-limit:
DISPLAY customer.
END.
FOR EACH customer BY credit-limit:
DISPLAY customer.
LEAVE.
END.
FOR LAST customer BY credit-limit:
DISPLAY customer.
END.
A procedure that displays the customer with the highest credit-limit looks like
the
following.
FOR EACH customer BY credit-limit DESCENDING:
DISPLAY customer.
LEAVE.
END.
________________________________
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> on behalf
of Chris Thompson
Sent: Tue 8/31/2010 11:28 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject - Email found in subject
It seems to be based on something other than OrderNum, OrderDate or when they
were entered on to the table.
19/04/10 Order No 8319
31/08/10 Order No 12815
10/08/09 Order No 314
I would have expected if it was based on orderdate that the 'first' would be
10/08/09 and the 'last' would be 31/08/10.
Likewise if it was based on order number, I would have expected order 314 to be
first and order 12815 to be last.
The way that it is working is as follows:
Last - 10/08/09 - Order 314
First - 19/04/10 - Order 8319
Orders were added to the system in date order so 314 was the first and 12815 was
the last.
________________________________
From: Ken Williams
<kwilliams@... <mailto:kwilliams%40intermountainelectronics.com> <mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com> >
To: "vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> "
<vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> >
Sent: Tue, 31 August, 2010 16:21:30
Subject: RE: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject - Email found in subject
I believe the first & last is based on actual entry into the database? Perhaps
it's OrderNum based?
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On Behalf
Of
Chris Thompson
Sent: Tuesday, August 31, 2010 8:57 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject - Email found in subject
Hi Sean,
Thanks for your reply.
What would it be basing the 'first' and 'last' on?
You would have expected them all to drop into the OrderHed table in the correct
order.
How can I get around this? Any suggestions?
Thanks,
CHRIS
________________________________
From: Sean McDaniel
<smcdaniel@... <mailto:smcdaniel%40comtecsolutions.com> <mailto:smcdaniel%40comtecsolutions.com>
<mailto:smcdaniel%40comtecsolutions.com>
<mailto:smcdaniel%40comtecsolutions.com>
<mailto:smcdaniel%40comtecsolutions.com>
<mailto:smcdaniel%40comtecsolutions.com>>
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>;
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Sent: Tue, 31 August, 2010 15:39:36
Subject: RE: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject
Progress finds the first/last record before sorting.
________________________________
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> on behalf of
Chris Thompson
Sent: Tue 8/31/2010 10:31 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject
Hi Ken,
I 'thought' I had it set up and working well (until I did spot cecks on the
data).
The first one I tried.
The BAQ (and therefore the Crystal Report) said that the last order for a
customer was on 10/08/2009 (notice the 2009 not 2010).
I checked on the customer's screen, and this was actually their first order.
I went to the BAQ to make sure I had 'last' selected on the OrderHed table
(which I did).
I ran the BAQ again and it still said that the last order for the particular
customer was 10/08/2009.
I took the 'last' off the OrderHed and found the customer had 3 orders.
19/04/10 Order No 8319
31/08/10 Order No 12815
10/08/09 Order No 314
They are displaying in this order in the BAQ.
Therefor when I select 'last' it is choosing the correct one, but it is also the
wrong one as you would expect the 'last' order to be the 'last' order they
placed.
To confirm this is what was happening, I selected 'First' on the OrderHed table
and ran the report. This time it displayed 19/04/10 with order number 8319 which
is the top of the list.
Am I missing something, but I didnt think this is what is meant by First and
Last.
I thought if there was a link between two table and you were using eg CustNum as
the link, it would find any records in the OrderHed table with CustNum e.g. 1234
and then if you selected 'First' it would display the first relevant result for
that custnum in some kind of order (presumably 'date entered').
It isnt doing what I would have expected - or am I expecting something
different?
________________________________
From: Ken Williams
<kwilliams@... <mailto:kwilliams%40intermountainelectronics.com> <mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com> >
To: "vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> "
<vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> >
Sent: Thu, 26 August, 2010 13:20:19
Subject: RE: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject
Make the customer to orderhed join an outerjoin. This will give you dates, where
they exist, and empty fields where they don't. Then in your Crystal just
suppress anything with a date newer than "x" months, this should give you both
no orders and old orders.
Ken
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On Behalf
Of
Chris Thompson
Sent: Thursday, August 26, 2010 6:18 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject
Will this also pick up customers who havent ordered at all so there isn't a
record against their custnum in the orderhed table?
________________________________
From: Ken Williams
<kwilliams@... <mailto:kwilliams%40intermountainelectronics.com> <mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>>
To: "vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>"
<vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>>
Sent: Thu, 26 August, 2010 13:11:29
Subject: RE: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject
A BAQ that takes the Customer table and joins it to the OrderHed table using a
"last" join should give you the data set you want. Then use Crystal to filter
based on the OrderHed.OrderDate.
Ken
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>] On Behalf Of
Chris Thompson
Sent: Thursday, August 26, 2010 2:58 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject
Hi all,
Does anyone have a report or dashbaord which shows a list of customers who
havent ordered in 'x' months?
Even better still, a list of customers who havent requested a quote or ordered
within x months.
If so, are you willing to share?
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
As a way to measure customer retention / loyalty loss I ran a report to show customers who have ordered more than ten times since 2007, and not once in the last year. It uses ODBC and is not pretty as I used it for exporting to Excel for further analysis.
I will send to you offline.
Bruce B.
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Chris Thompson
Sent: Wednesday, September 01, 2010 5:28 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Report to show customers who havent ordered in x months
HI All,
I've tried your suggestions but I cant seem to get it to work.
I mean, it will work, but not how I want it to.
I want to group my results by month so I can see which month was the last month
customers bought so the report will have a summary page as follows ...
Bought this month
Bought Last Month
Bought 2 Months Ago
etc
I can get it working like that using the data (which I thought was correct) from
Epicor. But the 'last' filter wasnt working as expected.
The only way I can see how to do this is to sort the data correctly in Epicor
before exporting it.
DO you know how I can sort if properly in Epicor BAQ so it will export the last
order per customer and customers with no orders?
Thanks.
________________________________
From: Chris Thompson <chriselectrix@... <mailto:chriselectrix%40ymail.com> >
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Sent: Wed, 1 September, 2010 8:17:58
Subject: Re: [Vantage] Report to show customers who havent ordered in x months
Hi All
Thanks for your suggestions.
I am going to try the simpler ones first (mainly because words like
"concatenate" scare me)!
I will let you know how I get on.
Thanks,
CHRIS
________________________________
From: Roel Martinez <rmartinez@... <mailto:rmartinez%40sabre-sts.com> >
To: "vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> " <vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> >
Sent: Tue, 31 August, 2010 23:30:02
Subject: RE: [Vantage] Report to show customers who havent ordered in x months
I think Sean’s option is good and simple….
It works! Even the proper Order Number shows –depending on your Order Date
sorting direction (ascending or descending).
Great idea Sean…
Regards.
Roel Martinez
ERP Analyst
SABRE TUBULAR STRUCTURES â„¢
www.sabretubularstructures.com<http://www.sabretubularstructures.com/>
8653 E. Hwy 67 | Alvarado, TX 76009
Phone: 817-852-1700 ext. 11858 | Direct Line: 817-852-1858 |Fax: 817-852-1705
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On Behalf Of Sean
McDaniel
Sent: Tuesday, August 31, 2010 5:03 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Report to show customers who havent ordered in x months
Try this:
Create a new report joining Customer to OrderHed. (do this by ODBC or BAQ
Report)
Create a grouping by customer.
Add an order by OrderHed.StartDate descending.
In the customer group header put in the customer information and the
ordered.orderdate fields.
Suppress the detail section. Suppress the Customer Footer.
Add in a suppression formula on the customer header such that it suppresses
customers whose ordered.orderdate < 6 months ago
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>] On Behalf Of
Roel Martinez
Sent: Tuesday, August 31, 2010 4:43 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Report to show customers who havent ordered in x months
Hello Chris, all.
If you can afford bringing all your Customer’s Orders to Crystal, you could try
a little of a “dirty trick†to select the maximum Order Date, together with the
proper Order Number; if you concatenate the Order Date and the order number, you
can summarize by Customer, create a MAX Summary based on the concatenated field
(OrderDate + OrderNumber) and then extract your Order Number at the Group Header
level for your Customer, from the MAX concatenated field.
The concatenation formula would be something like: totext({orderhed.orderdt},
“yyyymmddâ€) + “-“ + totext({orderhed.ordernum}, “00000000â€)
In this formula, the “yyyymmdd†will ensure you can summarize (MAX) the date and
the “0000000†string will allow you to extract your order number with a RIGHT
function.
Don’t know if it’s more complicated than what you are looking for, but it worked
for me in a report. Contact me directly if you have any questions.
Regards.
Roel Martinez
ERP Analyst
SABRE TUBULAR STRUCTURES â„¢
www.sabretubularstructures.com<http://www.sabretubularstructures.com/>
8653 E. Hwy 67 | Alvarado, TX 76009
Phone: 817-852-1700 ext. 11858 | Direct Line: 817-852-1858 |Fax: 817-852-1705
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On Behalf Of Chris Thompson
Sent: Tuesday, August 31, 2010 10:41 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject - Email found in subject
Hi Sean
Sounds complicated.
In simple terms, do you know how to link the Customer table and the OrderHed
table to display all customers (so each customer has one entry in the results)
and only to show the last ordernum per customer if a customer has had an order
at all.
e.g.
raw results
cust1 order1
cust1 order2
cust2
cust3 order3
cust3 order4
cust3 order5
cust4
the results I want
cust1 order2
cust2
cust3 order5
cust4
Any thoughts would be appreciated. I only know enough to do things in BAQ
designer and Crystal - I dont want to start messing the ODBC or Progress.
Thanks in advance.
________________________________
From: Sean McDaniel
<smcdaniel@... <mailto:smcdaniel%40comtecsolutions.com> <mailto:smcdaniel%40comtecsolutions.com>
<mailto:smcdaniel%40comtecsolutions.com>
<mailto:smcdaniel%40comtecsolutions.com>>
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>;
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Sent: Tue, 31 August, 2010 16:31:22
Subject: RE: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject - Email found in subject
It's based on the index that is being used.
The OpenEdge documentation provided indicates:
FIRST
Uses the criteria in the record-phrase to find the first record in the table
that meets that
criteria. Progress finds the first record before any sorting.
FOR FIRST customer BY credit-limit:
DISPLAY customer.
END.
The procedure above displays customer 1 (cust-num is the primary index of the
customer
table), not the customer with the lowest credit-limit. A procedure that displays
the
customer with the lowest credit-limit looks like the following.
FOR EACH customer BY credit-limit:
DISPLAY customer.
LEAVE.
END.
LAST
Uses the criteria in the record-phrase to find the last record in the table that
meets that
criteria. Progress finds the last record before sorting.
The procedure above displays the customer with the highest customer number
(cust-num
is the primary index of the customer table), not the customer with the highest
credit-limit.
FOR FIRST customer BY credit-limit:
DISPLAY customer.
END.
FOR EACH customer BY credit-limit:
DISPLAY customer.
LEAVE.
END.
FOR LAST customer BY credit-limit:
DISPLAY customer.
END.
A procedure that displays the customer with the highest credit-limit looks like
the
following.
FOR EACH customer BY credit-limit DESCENDING:
DISPLAY customer.
LEAVE.
END.
________________________________
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com> on behalf
of Chris Thompson
Sent: Tue 8/31/2010 11:28 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject - Email found in subject
It seems to be based on something other than OrderNum, OrderDate or when they
were entered on to the table.
19/04/10 Order No 8319
31/08/10 Order No 12815
10/08/09 Order No 314
I would have expected if it was based on orderdate that the 'first' would be
10/08/09 and the 'last' would be 31/08/10.
Likewise if it was based on order number, I would have expected order 314 to be
first and order 12815 to be last.
The way that it is working is as follows:
Last - 10/08/09 - Order 314
First - 19/04/10 - Order 8319
Orders were added to the system in date order so 314 was the first and 12815 was
the last.
________________________________
From: Ken Williams
<kwilliams@... <mailto:kwilliams%40intermountainelectronics.com> <mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com> >
To: "vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> "
<vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> >
Sent: Tue, 31 August, 2010 16:21:30
Subject: RE: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject - Email found in subject
I believe the first & last is based on actual entry into the database? Perhaps
it's OrderNum based?
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On Behalf
Of
Chris Thompson
Sent: Tuesday, August 31, 2010 8:57 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject - Email found in subject
Hi Sean,
Thanks for your reply.
What would it be basing the 'first' and 'last' on?
You would have expected them all to drop into the OrderHed table in the correct
order.
How can I get around this? Any suggestions?
Thanks,
CHRIS
________________________________
From: Sean McDaniel
<smcdaniel@... <mailto:smcdaniel%40comtecsolutions.com> <mailto:smcdaniel%40comtecsolutions.com>
<mailto:smcdaniel%40comtecsolutions.com>
<mailto:smcdaniel%40comtecsolutions.com>
<mailto:smcdaniel%40comtecsolutions.com>
<mailto:smcdaniel%40comtecsolutions.com>>
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>;
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Sent: Tue, 31 August, 2010 15:39:36
Subject: RE: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject
Progress finds the first/last record before sorting.
________________________________
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> on behalf of
Chris Thompson
Sent: Tue 8/31/2010 10:31 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject
Hi Ken,
I 'thought' I had it set up and working well (until I did spot cecks on the
data).
The first one I tried.
The BAQ (and therefore the Crystal Report) said that the last order for a
customer was on 10/08/2009 (notice the 2009 not 2010).
I checked on the customer's screen, and this was actually their first order.
I went to the BAQ to make sure I had 'last' selected on the OrderHed table
(which I did).
I ran the BAQ again and it still said that the last order for the particular
customer was 10/08/2009.
I took the 'last' off the OrderHed and found the customer had 3 orders.
19/04/10 Order No 8319
31/08/10 Order No 12815
10/08/09 Order No 314
They are displaying in this order in the BAQ.
Therefor when I select 'last' it is choosing the correct one, but it is also the
wrong one as you would expect the 'last' order to be the 'last' order they
placed.
To confirm this is what was happening, I selected 'First' on the OrderHed table
and ran the report. This time it displayed 19/04/10 with order number 8319 which
is the top of the list.
Am I missing something, but I didnt think this is what is meant by First and
Last.
I thought if there was a link between two table and you were using eg CustNum as
the link, it would find any records in the OrderHed table with CustNum e.g. 1234
and then if you selected 'First' it would display the first relevant result for
that custnum in some kind of order (presumably 'date entered').
It isnt doing what I would have expected - or am I expecting something
different?
________________________________
From: Ken Williams
<kwilliams@... <mailto:kwilliams%40intermountainelectronics.com> <mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com> >
To: "vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> "
<vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> >
Sent: Thu, 26 August, 2010 13:20:19
Subject: RE: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject
Make the customer to orderhed join an outerjoin. This will give you dates, where
they exist, and empty fields where they don't. Then in your Crystal just
suppress anything with a date newer than "x" months, this should give you both
no orders and old orders.
Ken
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On Behalf
Of
Chris Thompson
Sent: Thursday, August 26, 2010 6:18 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: Re: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject - Email found in subject
Will this also pick up customers who havent ordered at all so there isn't a
record against their custnum in the orderhed table?
________________________________
From: Ken Williams
<kwilliams@... <mailto:kwilliams%40intermountainelectronics.com> <mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>
<mailto:kwilliams%40intermountainelectronics.com>>
To: "vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>"
<vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>>
Sent: Thu, 26 August, 2010 13:11:29
Subject: RE: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject
A BAQ that takes the Customer table and joins it to the OrderHed table using a
"last" join should give you the data set you want. Then use Crystal to filter
based on the OrderHed.OrderDate.
Ken
From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>] On Behalf Of
Chris Thompson
Sent: Thursday, August 26, 2010 2:58 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Report to show customers who havent ordered in x months -
Email found in subject
Hi all,
Does anyone have a report or dashbaord which shows a list of customers who
havent ordered in 'x' months?
Even better still, a list of customers who havent requested a quote or ordered
within x months.
If so, are you willing to share?
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]