getting prior month data 1

[patty1]

My report has two parameters - a startdate and an enddate. Besides getting the data for the date range the user puts in (ie, 7/1/03 to 7/31/03), I also need prior months data for another column. I have done this in a sp but cannot figure out how to do it in Select Expert. Here is the (incorrect) formula:

{Transaction_History.Trade_Class} = "R" and
(({Transaction_History.Fund} in "0100" to "0399&quot or {Transaction_History.Fund} = '0744') and
{Transaction_History.Post_Date} in (month,{?StartDate}()-1) to {?EndDate}

Have tried several different ways but always get some sort of error. Am I close?

 

[Naith]

The last line should be:

{Transaction_History.Post_Date} in DateAdd('m',{?StartDate},-1) to {?EndDate})

Naith

 

[lbass]

Naith--

You mean:

{Transaction_History.Post_Date} in DateAdd('m',-1, {?StartDate}) to {?EndDate}

-LB

 

[patty1]

I should have told you that I am in Cr7 - in any case I fooled around with what you gave me and got it to work on:

{Transaction_History.Post_Date} in Date(Year({?StartDate}),Month({?StartDate}-1),Day({?StartDate})) to {?EndDate} - so when the user puts in start date:8/1/03 and enddate 8/31/03, I get all data from 7/103 to 8/31/03.

However, in the columns the gross amount is coming down no matter if the date is august or july. Here is what I have in the current month column:

{Transaction_History.Post_Date} in Date(Year({?StartDate}),Month({?StartDate}+1),Day({?StartDate})) to {?EndDate} then {Transaction_History.Gross_Amount}

I have the same in previous month column except -1.

Data is same in both!

 

[lbass]

For the current month, the formula should look like:

If {Transaction_History.Post_Date} in {?StartDate} to {?EndDate} then {Transaction_History.Gross_Amount}

For the previous month, try something like:

If month({?StartDAte}) = 1 then
(if {Transaction_History.Post_Date} in Date(year({?StartDate})-1, 12, day({startdate})) to {?StartDate}-1 then {Transaction_History.Gross_Amount}) else
if month({?StartDate}) <> 1 then
(if {Transaction_History.Post_Date} in Date(year({?StartDate}), month({?StartDate})-1, day({startdate})) to
{?StartDate}-1 then {Transaction_History.Gross_Amount})

-LB

 

[patty1]

The current formula works but the previous is still combining both months - any ideas?

 

[lbass]

Please copy the exact formulas you are using into a post, and also give an example of the values you are inputting for the parameters, and a sample of the results you want to see.

-LB

 

[patty1]

Here is the original formula for the previous month

if {Transaction_History.Post_Date} in LastFullMonth and

({Transaction_History.Dealer_Number} = &quot;40158&quot; and {Rep_Profile.Territory} = &quot;00&quot;
and {Transaction_History.Trade_Type}='R')

or {Transaction_History.Dealer_Number} <> &quot;40158&quot; and {Transaction_History.Trade_Type}='R'
and not ({Rep_Profile.Territory} in [&quot;00&quot;,&quot;85&quot;, &quot;88&quot;, &quot;89&quot;, &quot;90&quot;])


or ({Transaction_History.Dealer_Number} ='0263' and {Transaction_History.Trade_Type}='R')
then {Transaction_History.Gross_Amount}

the parameters values are dates 8/1/03 = StartDate
8/31/03 = EndDate

an example of results is 3,004,402

Hope this is what you meant. Thanks for taking the time to help me!

 

[lbass]

That helps, but please also provide your selection formula, and if you used my suggestion for the previous month, please copy that formula as you used it in here, too. This should be easily solved once we have all the info.

-LB

 

[patty1]

Again I hope this is what you mean - my selection formula

{Transaction_History.Trade_Class} = &quot;R&quot; and
(({Transaction_History.Fund} in &quot;0100&quot; to &quot;0399&quot or {Transaction_History.Fund} = '0744') and
not ({Transaction_History.Dealer_Number} in [&quot;00418&quot;, &quot;0354&quot;]) and
{Transaction_History.Post_Date} in Date(Year({?StartDate}),Month({?StartDate}-1),Day({?StartDate})) to {?EndDate}


your formula in previous month (this is the one capturing both months)

If month({?StartDate}) = 1 then
(if {Transaction_History.Post_Date} in Date(year({?StartDate})-1, 12, day({?StartDate}))
to {?StartDate}-1 then {Transaction_History.Gross_Amount}) else
if month({?StartDate}) <> 1 then
if {Transaction_History.Post_Date} in Date(year({?StartDate}), month({?StartDate})-1, day({?StartDate})) to
{?StartDate}-1 and


({Transaction_History.Dealer_Number} = &quot;40158&quot; and {Rep_Profile.Territory} = &quot;00&quot;
and {Transaction_History.Trade_Type}='P') or
{Transaction_History.Dealer_Number} <> &quot;40158&quot; and {Transaction_History.Trade_Type}='P'
and not ({Rep_Profile.Territory} in [&quot;00&quot;,&quot;85&quot;, &quot;88&quot;, &quot;89&quot;, &quot;90&quot;])
or ({Transaction_History.Dealer_Number} ='0263' and {Transaction_History.Trade_Type}='P')
and
{Transaction_History.Trade_Type}='P' then {Transaction_History.Gross_Amount}

 

[lbass]

The problem is with your &quot;or&quot; statements. It isn't reading the additional criteria after the &quot;or's&quot; as relating back to the date criteria, but instead as if the dates don't apply, which results in the selection of all data within the record selection formula date period. This is because of missing parentheses, so, try something like this:

If month({?StartDate}) = 1 then
(if {Transaction_History.Post_Date} in Date(year({?StartDate})-1, 12, day({?StartDate}))
to {?StartDate}-1 and
(
({Transaction_History.Dealer_Number} = &quot;40158&quot; and {Rep_Profile.Territory} = &quot;00&quot;
and {Transaction_History.Trade_Type}='P') or

({Transaction_History.Dealer_Number} <> &quot;40158&quot; and {Transaction_History.Trade_Type}='P'
and not ({Rep_Profile.Territory} in [&quot;00&quot;,&quot;85&quot;, &quot;88&quot;, &quot;89&quot;, &quot;90&quot;])) or

({Transaction_History.Dealer_Number} ='0263' and {Transaction_History.Trade_Type}='P')
)
then {Transaction_History.Gross_Amount}) else

if month({?StartDate}) <> 1 then
(if {Transaction_History.Post_Date} in Date(year({?StartDate}), month({?StartDate})-1, day({?StartDate})) to
{?StartDate}-1 and
(
({Transaction_History.Dealer_Number} = &quot;40158&quot; and {Rep_Profile.Territory} = &quot;00&quot;
and {Transaction_History.Trade_Type}='P') or

({Transaction_History.Dealer_Number} <> &quot;40158&quot; and {Transaction_History.Trade_Type}='P'
and not ({Rep_Profile.Territory} in [&quot;00&quot;,&quot;85&quot;, &quot;88&quot;, &quot;89&quot;, &quot;90&quot;])) or

({Transaction_History.Dealer_Number} ='0263' and {Transaction_History.Trade_Type}='P')

then {Transaction_History.Gross_Amount})

You also seemed to have an extra line &quot;and {Transaction_History.Trade_Type}='P'&quot; which I removed.

I think this should work.

-LB

 

[patty1]

Can't thank you enough - don't think I ever would have gotten this on my own