| Author |
Message |
surly
Newbie
Joined: 28 Aug 2012
Location: United States
Online Status: Offline
Posts: 5
|
  
Topic: Help with date ranges
Posted: 28 Aug 2012 at 10:45am |
|
I need to do a report which will look at customer's orders. I need it to give me a list of customers that have ordered in the last 6 months, but not in the 6 months prior to that. I've created this formula trying to get those results, but it doesn't seem to be working. Am I going about this completely wrong?
if {@Date} <> (DateAdd ("m", -6, {?Start Date})) to (DateAdd ("m", -12, {?Start Date})) and
{@Date} in {?Start Date} to (DateAdd ("m", -6, {?Start Date})) then
{table.CUST_NUMB}
else ''
@Date is the order date and ?Start Date would be any date we'd want to start the search from.
Edited by surly - 28 Aug 2012 at 10:47am
|
 IP Logged |
|
|
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 28 Aug 2012 at 11:00am |
|
The code looked good to me, so I tried on a database I had access to (of course I had to change field names). But it appears that you need to replace the 'And' with an 'Or'. Of course I am speculating on what the issue is.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Aug 2012 at 11:06am |
you would have to do a grouping to get this
your main select would pull one year of data
{table.orderdate} in dateadd('yyyy',-1,{?date}) to {?date}
group on customer
now you can use a select statement on group conditions
minimum({table.orderdate},{table.customer}) in dateadd('m',-6,{?date}) to {?date} and maximum({table.orderdate},{table.customer}) in dateadd('m',-6,{?date}) to {?date}
Opps, Sorry KevlRay- I was typing and did not see your posting. Edited by DBlank - 28 Aug 2012 at 11:12am
|
| IP Logged |
|
surly
Newbie
Joined: 28 Aug 2012
Location: United States
Online Status: Offline
Posts: 5
|
Posted: 28 Aug 2012 at 11:34am |
Originally posted by kevlray
The code looked good to me, so I tried on a database I had access to (of course I had to change field names). But it appears that you need to replace the 'And' with an 'Or'. Of course I am speculating on what the issue is.
Thanks for the tip, but I don't believe that would work. I need both conditions to be met in order to get the correct results.
|
| IP Logged |
|
surly
Newbie
Joined: 28 Aug 2012
Location: United States
Online Status: Offline
Posts: 5
|
Posted: 28 Aug 2012 at 11:35am |
Originally posted by DBlank you would have to do a grouping to get this
your main select would pull one year of data
{table.orderdate} in dateadd('yyyy',-1,{?date}) to {?date}
group on customer
now you can use a select statement on group conditions
minimum({table.orderdate},{table.customer}) in dateadd('m',-6,{?date}) to {?date} and maximum({table.orderdate},{table.customer}) in dateadd('m',-6,{?date}) to {?date}
Opps, Sorry KevlRay- I was typing and did not see your posting.
I'll give this a shot. Thanks for the replies!
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 28 Aug 2012 at 11:40am |
|
note: if you do more summarizations after the group select you will need to use running totals or variable formulas
|
| IP Logged |
|
surly
Newbie
Joined: 28 Aug 2012
Location: United States
Online Status: Offline
Posts: 5
|
Posted: 28 Aug 2012 at 12:03pm |
|
DBlank, your solution seemed to work! I still need to double check some data, but THANK YOU! I've struggled with this report for hours. I'm glad I found this place.
|
| IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 28 Aug 2012 at 12:39pm |
|
I glad you got a solution. I tried the modified formula (in a simplified setting using an OR). From what I what getting, it was returning a value when the @Date was within six months and older than 12 months from the ?Start Date. So maybe both solutions work?!?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Aug 2012 at 4:33am |
|
FYI- IMO there was nothing wrong with the original formula. It would execute and do what it was written to do. IMO what was wrong was the the solution process in general. The original formula was set to evaluate each row not multiple rows. The need was to do a comparison across rows for one customer hence a suggestion to use group level summary in the group select statement. I could not see how the original formula gave any useful data to check across the group for the needed range comparison. Perhaps it can work, I just don't see it though.
|
| IP Logged |
|
surly
Newbie
Joined: 28 Aug 2012
Location: United States
Online Status: Offline
Posts: 5
|
Posted: 29 Aug 2012 at 7:44am |
|
Ahh ok, thanks for the info, I appreciate it.
|
| IP Logged |
|
|
|
