Print Page | Close Window

Counting range with gaps in records

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1737
Printed Date: 04 May 2024 at 7:07am


Topic: Counting range with gaps in records
Posted By: johnstk
Subject: Counting range with gaps in records
Date Posted: 22 Nov 2007 at 12:46am
Trying to calculate Days In Stock value:
I have an opening quantity value (@fOpQtyBal) at the beginning of a period (displayed in Group header). Detail section lists all quantity movements for the period by date. I need to calculate the number of days that there is positive stock (Balance > 0).
Data looks like this:
                             Qty    Balance
fOpQtyBal              10             10
01/01/2007            -1               9
03/01/2007            -9               0
10/01/2007             6               6
 
Days in stock is 31 days in January less 7 days (03/01 - 10/01) where there was no stock = 24.
 
Have tried a few ideas but get stuck on looping through the days because there are some days where there are no records. Any heads up?


Replies:
Posted By: BrianBischof
Date Posted: 22 Nov 2007 at 1:35pm
You're going to have to use a manual running total. It's not hard, but it will be a bit tricky if you aren't familiar with writing formulas. Here is something quick I wrote that might get you started in the right direction. It will probably be off by a day or two, but you can test it and tweak it to get it right.
Global NumberVar Balance;
Global NumberVar DaysInStock;
Balance := Balance + {qty};
if Balance > 0 Then
    DaysInStock := DaysInStock + DateDiff("d", {fOpQtyBal}, PreviousValue(fOpQtyBal});

If you are looking for more information on all the CR functions and how to write formulas, my CR Encyclopedia book has over 50 pages to help you understand program and debug them.


-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>


Print Page | Close Window