Value at Max Date for each month of the year

[JasonKaufman]

I'm using Crystal XI
I am wanting to create a report which shows the inventory value of a product for each month of a given year.
The value of the product will change randomly depending on when goods were sold.

For example, a product valuation for January 2018 could return the valuation from 06/20/2016 because that was the last valuation date created for this product prior to 01/31/2018.
then for February 2018 & March 2018, the valuation could be based from the then last record of 02/06/2018.

The data is from one table. The fields being used are product-code, created-date, & product-value. I imagine that I will be grouping on product-code.

Thanks in advance for the assistance!

 

[kray4660]

Sounds like you would group on product code and created date(?), I am assuming that the created date would be a new valuation. Suppress details and Group Headers and the created date Group Footer. Then in the product Group Footer, have what fields you need. This should (provided I understand the data correctly) give you the last valuation for a product.

 

[lbass]

I can't really test this entirely, but try the following. First create parameters for the beginning and end dates of the period containing all known valuations and then create a formula as follows:

//{@dtarray}:
whilereadingrecords;
numbervar j := datediff("d",{?BeginningDate},{?Enddate})+1;
datevar array RangeDates;
datevar k := date(0,0,0);
numbervar i := i + 1;
if i <= j then
(
redim preserve RangeDates[j];
RangeDates := {?BeginningDate}+i-1;
k := RangeDates
);

Insert a group on the array formula on change of month.

Then insert a subreport containing your fields.

Place the subreport in the group header and add a link on {@dtarray} but uncheck "select data in subreport based on field".

Add a selection formula in the subreport like this:

month({table.createddate})>=month({?pm-@dtarray}) //this ensures there is a value in each month group

Next sort records within the subreport by created date in ascending order and then create a formula inside the subreport as follows:

whileprintingrecords;
shared numbervar currval;
if month({tablecreateddate})=month({?Pm-@dtarray}) then
currval := {table.productvalue} else
currval := currval;

If product value is a currency, replace "numbervar" with "currencyvar". Place this in the detail section and then suppress the detail section. To see the value at the end of each month, put the following formula in the subreport footer:

//{@display}:
whileprintingrecords;
shared numbervar currval;

Suppress the other sections of the subreport.

In the main report, go to format->edit subreport->subreport tab->check "suppress blank subreport". Also remove the borders on the subreport, and in the section expert select the group header and check "suppress blank section".

In the subreport, format the display formula in the footer (format field->suppress->x+2 and enter: currentfieldvalue=0).

-LB