Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Trying to sort date by Minimum Difference Post Reply Post New Topic
Author Message
frozentech
Newbie
Newbie
Avatar

Joined: 06 Mar 2013
Location: United States
Online Status: Offline
Posts: 5
Quote frozentech Replybullet Topic: Trying to sort date by Minimum Difference
    Posted: 06 Mar 2013 at 7:14am
So I'm new to this forum, and I'm not exactly a Crystal Wizkid, but my job requires me to edit and occasionally build Crystal Reports.  For the most part its straight forward, but then I get a request like this one.
 
I need to add a current payment field to a rather complex report from a completely new table.  Pulling the table and joins was easy enough, but the report owner would like the payment displayed to be the one *closest* to the day the report is run - and its run... at any given day.
 
I found a chunk of code someone else used as an exemplar to use minimum difference but I haven't been able to figure out how to make it work for me.  So here's what I've got to work with.
 
The Current Date.  The Due Date (which is the only date field on the new table.  And of course the Payment.  What I think I want to do is add a selection criteria with a formula that determines the minimum difference between the current date and the due date.  But building that formula is a bit beyond me.
 
Any takers in giving a newb a hand?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Mar 2013 at 8:11am

so your new table has multiple rows that you are joining to. I would assume a payment cannot be made in the future so you just need the max value from the this new payment table. Can you use a stored proc or a view to get th amx value that way then joni tha into the report and your problem is solved.



Edited by DBlank - 06 Mar 2013 at 8:12am
IP IP Logged
frozentech
Newbie
Newbie
Avatar

Joined: 06 Mar 2013
Location: United States
Online Status: Offline
Posts: 5
Quote frozentech Replybullet Posted: 06 Mar 2013 at 8:42am
This is a report of current leases held by the company.  They want to see the the payment closest to the current date - be that future or past.
 
So what I came up with is this.  (And I haven't solved it yet)
I created a field called Date Difference which has the current formula:
 
Abs (DateDiff ("d",CurrentDateTime ,{LMPAYDTL.DUE_DATE} ))
 
This should give me the number of dates between the current date, and Due_Date.   My thought is I then take a minimum of thise formula field
 
minimum({@Date Difference}) 
 
This gives me the base value I need.  I then Add/subtract from the Current Date, and whichever current date time matches {LMPAYDTL.DUE_DATE} will give me the correct selection criteria.  My only problem is I'm now not quite sure how to get from my Minimum step to the idea outlined above.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Mar 2013 at 8:50am
what is defined as clsoset to today (date run) when there is a future date? is it always the min absolute value of date diff or is the last payment no matter how far into the future?
IP IP Logged
frozentech
Newbie
Newbie
Avatar

Joined: 06 Mar 2013
Location: United States
Online Status: Offline
Posts: 5
Quote frozentech Replybullet Posted: 06 Mar 2013 at 8:58am
The way it was explained to me, is they want the nearest payment to the day the report is run.
 
So if the payment happens on the 1st of every month, and its the 12th of March, the payment from March 1st should display.
 
If the report is pulled on March 17th the payment for April 1st should display.
 
If payments were always on the same day, this would probably be simpler.  Unfortunately... this isn't the case.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Mar 2013 at 9:54am
So you might consider just suppressing the data or sections.
Do everything you did but use a grouping on the lease field or whatever the payments are applicable to.
chaneg your MIN formala to be per group (lease) 
minimum({@Date Difference},table.lease)
 
now you can conditionally suppress the detail sections using the MIN to compare tothe actual value
minimum({@Date Difference},table.lease)<> {@Date Difference}
You can also suppress the group headers and footers if you do not need them
 


Edited by DBlank - 06 Mar 2013 at 9:55am
IP IP Logged
frozentech
Newbie
Newbie
Avatar

Joined: 06 Mar 2013
Location: United States
Online Status: Offline
Posts: 5
Quote frozentech Replybullet Posted: 06 Mar 2013 at 11:42am
Thanks a bunch for the help so far, I'm within spitting distance, but I'm running into an issue.
 
I've created a group based on TABLE.LEASE and three formulas, which look like this:
 
First: Date Difference.  This gives me  an Absolute value for the various differences.  It has to be absolute or Minimum will always return the lowest negative value.
Abs (DateDiff ("d",CurrentDateTime ,{TABLE.DUE_DATE}))
 
then Date Difference 2 which gives me the minimum absolute value based off of the Lease section of the table.  This works perfectly.
minimum ({@Date Difference},{TABLE.LEASE})
 
Finally we have this monster, Date Difference 3, which *should* return my Due Date (and does, in 95% of cases... but...)
 
If DateAdd("d",{@Date Difference 2},CurrentDate)=DateValue({TABLE.DUE_DATE}) then DateValue({TABLE.DUE_DATE}) else
DateAdd("d",-{@Date Difference 2},CurrentDate)
 
The problem is the Original Add function at the begining is never working as intended, so it always ends up satisfying the Else.
 
If I can get Difference 3 to work as intended I'll beable to use that formula field as a Selection Formula for the records and get the proper payments listed.
 
At least thats my intention.
 
 
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Mar 2013 at 11:52am

I believe the problem is that you cannot pull the data, calculate it at the grouplevel (to get the value you need for the select criteria) and then apply that calculated value to exclude rows that were part of the calculation to begin with.

You can use group select criteria somwwhat in this manner but not the record select. Thsiis why I suggested to either get the value in a stored proc and filter before you get it into the report or to suppress unwanted rows rather than try to exclude them.


Edited by DBlank - 06 Mar 2013 at 11:53am
IP IP Logged
frozentech
Newbie
Newbie
Avatar

Joined: 06 Mar 2013
Location: United States
Online Status: Offline
Posts: 5
Quote frozentech Replybullet Posted: 07 Mar 2013 at 11:52am
So, thanks to DBlank, a lot of banging my head, and a thought from another co-woker I solved this issue. 
 
I created an extra group in the report (which I surpressed) and used the following as my selection formula:
 
Abs(DateDiff("d",CurrentDate,{Table.Due_Date}))<=14
 
15 is probably better, but in Febuaray it would return 2 dates. 
 
14 isn't perfect (there's a couple of days in there on 31 day months where the report probably won't return correctly.
 
However... I'll take the chance :)
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.