get totals based on parameter date before and after

[rrmcguire]

What Im trying to do is display then sum of a field, net_total, for a set of records for 6 months before and 6 months after a specific date, which is set as a parameter field the user enters , which should be equal to a field, sales_trip_date. But the records for the field net_total, have a date assigned to them, pick_up_date, which needs to be between, the date 6 months prior to parameter date and the parameter date, then between the parameter date and 6 months after.

So, Im trying to do a running total where the pickup date is between the date 6 months prior to parameter and parameter and between the parameter and 6 months after.

 

[Turkbear]

One way would be to create a formula that tests the Date of the net_total and either returns the net_total amount or 0, like:
@NetSum

If pick_up_date in DateAdd ("m", -6, {pick-up_date}) to DateAdd ("m", 6, {pick-up_date}) 
THEN
{net_total}
ELSE
0

The create a running total or a SUM using this formula.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rrmcguire]

well looking at that it would seem that it would work but isn't. So far I have:


if {V_SALESCALLS_TTX.PICKUPDATE} in DateAdd ("m", -6,{?Pm-Magic Date}) to {?Pm-Magic Date} then {V_SALESCALLS_TTX.NET_TOTAL} else 0

then a running total on that with sum of net_total evaluating on the above.

then for the time after doing this:


if {V_SALESCALLS_TTX.PICKUPDATE} in {?Pm-Magic Date} to DateAdd ("m", 6, {?Pm-Magic Date}) then {V_SALESCALLS_TTX.NET_TOTAL} else 0

then a running total on that with sum of net_total evaluating on it.

But its not pulling anything whereas there should be two records with one before and one after.

Any Help would be appreciated

 

[Turkbear]

Hi,
That is not a record selection formula, so be sure not to use it as such..It should be in the Detail section of your report.

You can supress its display if you do not need to see each a net_total for each record.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
Sorry, ignore that last posting, I missed something in your initial post, try this:

If pick_up_date in DateAdd ("m", -6, {?ParameterDateEntered}) to DateAdd ("m", 6, {?ParameterDateEntered}) 
THEN
{net_total}
ELSE
0

You can SUM that formula for a total of the net_totals that fall withing that date range.
BUT,I am not sure what you mean by

]
parameter field the user enters , which should be equal to a field, sales_trip_date

can you explain that further as to how it figures into finding the correct records?

Is it a selection criteria used to limit the records?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rrmcguire]

basically the user enters in the parameter field date, then based on if another date field is within 6 months prior to the parameter date to the parameter date, sum the "net_total" for all records for that range.

Then do the same for the range from the parameter field to 6 months after if that other date field is within that range.

 

[rrmcguire]

Im not trying to use it by suppressing the details section based on that though. From what Im seeing I would assuming if my pickup date is within my date range 6 months prior to parameter to parameter, then sum the net totals for those records,

what I have would work? It displays the records but the totals don't pull anything as I have running totals for before and after evaluating on this:

if {V_SALESCALLS_TTX.PICKUPDATE} in DateAdd ("m", -6,{?Pm-Magic Date}) to {?Pm-Magic Date} then {V_SALESCALLS_TTX.NET_TOTAL} else 0

for before, and this:

if {V_SALESCALLS_TTX.PICKUPDATE} in {?Pm-Magic Date} to DateAdd ("m", 6, {?Pm-Magic Date}) then {V_SALESCALLS_TTX.NET_TOTAL} else 0

for after.

 

[rrmcguire]

ok, on the last posting, yes, I need to suppress the records which aren't within the range 6 months prior to the parameter or from the parameter to 6 months after. The running totals at the end are correct, but I need to suppress the records which don't fall within that range.

 

[rrmcguire]

I got the totals to come up correctly for before and after the parameter date entered using

if {V_SALESCALLS_TTX.PICKUPDATE} in DateAdd ("m", -6,{?Pm-Magic Date}) to {?Pm-Magic Date} then true else false

for before and

if {V_SALESCALLS_TTX.PICKUPDATE} in {?Pm-Magic Date} to DateAdd ("m", 6, {?Pm-Magic Date}) then true else false

for after

but the shipments still show any date range in the details of the report regardless of whether its 6 months before or after the parameter date.

Any help would be appreciated.

thanks

 

[rrmcguire]

got everything figured out, this thread can be closed