Print Page | Close Window

Value at Max Date for each month of the year

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2016
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
Printed Date: 13 Aug 2020 at 3:16pm

Topic: Value at Max Date for each month of the year
Posted By: jkaufman
Subject: Value at Max Date for each month of the year
Date Posted: 23 Oct 2019 at 9:06am
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 on one row.

The issue is that there will not be a value for every month, only when it is sold does a record get created.  So it is necessary to pull the last record even if it is not within the given month.

For example, a product valuation for January 2018 could return the record  from 06/20/2016 because that was the last sell date for this product prior to 01/31/2018.  So if a report was for 2017, the value would be the same for all 12 months since the last record was from 2016.

Then for February 2018 & March 2018 columns, the valuation would be the last sell date record of 02/06/2018 and so on.

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

Thanks in advance for the assistance!

Posted By: DBlank
Date Posted: 29 Oct 2019 at 3:39am
not really knowing your data set consider using a running total that resets on change of product not date

Print Page | Close Window