Print Page | Close Window

Aged from current date

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19211
Printed Date: 07 May 2024 at 5:34am


Topic: Aged from current date
Posted By: Paula J
Subject: Aged from current date
Date Posted: 04 Mar 2013 at 6:42am
I created 6 formula one for 0-30 then 31-60, etc thru Over 150. I want the report to print the balance of an account in the correct bucket based on discharge date and then the other buckets report a 0.00.
 
My issue is if an account is over 150 days old the balance will result in all buckets vs just the last bucket.
if {PD_CDDPDRACCMAS_RECORD.CH_DIS_DT}>=currentdate-4000 to currentdate-151
then {PD_CDDPDRACCMAS_RECORD.CH_PT_DUE_AMT}
How it looks
Acct#   0-30    31-60  61-90  91-120  121-150  Over 150
11111  15.00  15.00  15.00   15.00     15.00      15.00
How I want it to look
Acct#   0-30  31-60  61-90  91-120  121-150  Over 150
111111  0.00  0.00  0.00     0.00       0.00        15.00
 
If an account is 93 days olf then the balance will result in 0-30,31-60,61-90 and 91-120 when I only want it to result in bucket 91-120.
if {PD_CDDPDRACCMAS_RECORD.CH_DIS_DT}>=currentdate-120 to currentdate-91
then {PD_CDDPDRACCMAS_RECORD.CH_PT_DUE_AMT}
 
 
How do I accomplish what I want?


-------------
Paula J


Replies:
Posted By: DBlank
Date Posted: 04 Mar 2013 at 7:08am
you can use datediff() or agedXtoXdays functions
Not sure if you want the value to only show once per bucket for each instance. If so look at 6 formulas, each returns a number or a 0 if the vaue is outside the day range of the formula. Place each forula in the respective 'column'
//0to30
if {PD_CDDPDRACCMAS_RECORD.CH_DIS_DT} in Aged0To30Days
then {PD_CDDPDRACCMAS_RECORD.CH_PT_DUE_AMT}
 
//31to60
if {PD_CDDPDRACCMAS_RECORD.CH_DIS_DT} in Aged31To60Days
then {PD_CDDPDRACCMAS_RECORD.CH_PT_DUE_AMT}
 
//61to90
if {PD_CDDPDRACCMAS_RECORD.CH_DIS_DT} in Aged61to90Days
then {PD_CDDPDRACCMAS_RECORD.CH_PT_DUE_AMT}
 
 
//91to120
if datediff('d',{PD_CDDPDRACCMAS_RECORD.CH_DIS_DT},currentdate) in 91 to 120
then {PD_CDDPDRACCMAS_RECORD.CH_PT_DUE_AMT}
 
//121to1500
if datediff('d',{PD_CDDPDRACCMAS_RECORD.CH_DIS_DT},currentdate) in 121 to 150
then {PD_CDDPDRACCMAS_RECORD.CH_PT_DUE_AMT}
 
//Over150
if datediff('d',{PD_CDDPDRACCMAS_RECORD.CH_DIS_DT},currentdate) >150
then {PD_CDDPDRACCMAS_RECORD.CH_PT_DUE_AMT}
 
 
 

Posted By: Paula J
Date Posted: 04 Mar 2013 at 7:57am
This works...thanks!

-------------
Paula J


Print Page | Close Window