Value of Maximum Date below date parameter

[aomara]

I have a table that includes Daily store Values.

I would like to get the opning balance of a certain period.

I have a date parameter, So the opning balance is the value of the (date parameter-1) or in other words MAXIMUM DATE BELOW THE date parameter.

I relay appreciates your help ?

 

[synapsevampire]

In the record selection-group formula, you might use:

{table.date} < {?MyDateParameter}
and
{table.date} = maximum({table.date})

Generally people show their intended report layout, for instance if you have different stores that you want the values by.

Stating that you have a table is a given, reports always have data sources, stating that layout and what is in the table is what's important.

I suggest that you post technical information:

Crystal version
Database/connectivity
Example data
Expected output

-k

 

[aomara]

OK, First, Thank you for your interest and fast replay
---------------------------------------------------
For the working environment, it's :
** Crystal Report 10
** ODBC on SQL Server 2000
---------------------------------------------------
A sample of data is as follows:

Store Date Value
===== ======== =====
01 01/06/2006 14290

01 03/06/2006 14500
01 04/06/2006 15000
01 05/06/2006 14890
01 06/06/2006 15120
01 07/06/2006 13900

01 09/06/2006 14290
01 10/06/2006 14020
01 11/06/2006 14450
01 12/06/2006 14598
---------------------------------------------------
Now, The report should retreive the store balance within perioud

Opening Balance #######

Issue #####
receive #####
Adjustments #####

Closing Balance #######
---------------------------------------------------
I have 2 parameters (from date) (to date)
Ex:
from date : 03/06/2006
To date : 08/06/2006

the opening Balance Should be the balance of 01/06/2006 (14290)
the clsoing Balance Should be the balance of 07/06/2006 (13900)
---------------------------------------------------
I think your solution is can be applicable with the clsoing balance, but I still have the problem with Opening Balance

 

[synapsevampire]

Yeah, that's quite a bit different than your original post.

How does one know what the Issue, Receive and Adjustments are? Is there another field that demonstrates this?

The initial post stated maximum, now you want the opening, which I would assume is the minimum.

And do you need all of the data, or only up until the end date parameter?

The record selection should be in the record portion only, and akin to:

{table.date} in {?from date} to {?to date}

Group by the store, and make sure that you're sorted by the date ascending (Under Report pull down).

Now you can use a formula for the opening and closing balances:

whileprintingrecords;
numbervar Opening;
numbervar Closing;
if {table.date} = maximum({table.date}, {table.group}) then
Opening:= {table.value}
else
if {table.date} = maximum({table.date}, {table.group}) then
Closing:= {table.value}

Then in the group footer display the results using 2 formulas, as in:

whileprintingrecords;
numbervar Opening

whileprintingrecords;
numbervar Closing

As for how you get the other values, you didn't show how they could be determined.

-k

 

[lbass]

To get the opening balance, I think you'd have to use a record selection formula based on a range that you believe will always contain the previous balance. For example, you might determine that there the most recent balance prior to the report range is always with a week earlier (or it could be a year), so then you would use a record selection formula like:

{table.date} in {?startdate}-7 to {?enddate}

Then sort the records by date ascending and use a formula like the following to get the initial balance:

if {table.date} in {?startdate} to {?enddate} then previous({table.value})

If you place this formula in the group header, it should show the opening balance.

Or you could insert a subreport in the report header where you specify:

{table.date} < {?start}

Then go to report->selection formula->group and enter:

{table.date} = maximum({table.date})

This should return the last record before {?start}. If there are multiple records on that date, you should sort by date and drag {table.value} into the subreport report footer and suppress all other report sections.

-LB

 

[synapsevampire]

Ooops, should have been:

whileprintingrecords;
numbervar Opening;
numbervar Closing;
if {table.date} = minimum({table.date}, {table.group}) then
Opening:= {table.value}
else
if {table.date} = maximum({table.date}, {table.group}) then
Closing:= {table.value}

 

[aomara]

Many Thanks for all, I realy appreciates this rich discussion.

I already do it by applying the first solution of synapsevampire with 2 sub reports - each for opening and closing.

Many thanks for all )