Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Conditional datediff formula Post Reply Post New Topic
Author Message
Beej
Newbie
Newbie
Avatar

Joined: 17 Dec 2013
Location: Australia
Online Status: Offline
Posts: 2
Quote Beej Replybullet Topic: Conditional datediff formula
     Posted: 18 Dec 2013 at 11:50am
Hi,
I'm using 2008 and am struggling with some calculations and hoping someone would be kind enough to help.
 
A record (person) can have more than one categorisations of their referral, with a date and category code being added to their record.  I need to count days the referral was UC (uncategorised). A scenario can be:
 
Patient 1   8/11/2013     UC
                 12/11/2013    AI
                  20/11/2013   UC
                  25/11/2013   2
 
So the result is 9 days UC.  I also have to take exclude weekends in the count. I have grouped on Patient, have a reset variable in the group header (suppressed) and SetTotal Days in the group footer:
 
whileprintingrecords;
Global numberVar TotalUCDays;
TotalUCDays:= TotalUCDays + (if previous ({OPDReferrals.Urno})={OPDReferrals.Urno} and
Previous ({OPDReferral__CatHistory.ReferralCategoryCode})="UC" then
 (DateDiff ('d',PREVIOUS({OPDReferral__CatHistory.CategorisationDate}),{OPDReferral__CatHistory.CategorisationDate}) -
    DateDiff ("ww", PREVIOUS({OPDReferral__CatHistory.CategorisationDate}), {OPDReferral__CatHistory.CategorisationDate}, crSaturday) -
    DateDiff ("ww", PREVIOUS({OPDReferral__CatHistory.CategorisationDate}), {OPDReferral__CatHistory.CategorisationDate}, crSunday)
)
    else 0
)
 
It appears to be working OK - please let me know if there is a more succinct way of doing.  I now need to summarise the resulting UC days but  am going around in circles.  I want to summarise by referral clinic and UC days <4 days and UC days >4, so this will show a trend from month to month (in select criteria). Tried a cross-tab and running totals, but the SetTotalDays formula doesn't show - I guess because cannot summarise a summary? 
 
Any light would be very much appreciated.
Thanks
B
IP IP Logged
Sastry
Moderator
Moderator
Avatar

Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
Quote Sastry Replybullet Posted: 18 Dec 2013 at 6:32pm
hi

If the formula which you have written was resulting good then it is fine and no need to change the formula.

Coming to summary to UC days, you want write a manual running totals to get it.

Example : If your below formula is written in @days (giving sample name)

Initialize a variable in group header :

Whileprintingrecords;
Numbervar Total_UC_Days:=0;  // place this in group header



Whileprintingrecords;
Numbervar Total_UC_Days;
Total_UC_Days:=Total_UC_Days+{@days};  // Place this in detail (where you are displaying difference of days)

Whileprintingrecords;
Numbervar Total_UC_Days;  //Place this formula in group footer.


It will give you the summary of UC Days.




Thanks,
Sastry
IP IP Logged
Beej
Newbie
Newbie
Avatar

Joined: 17 Dec 2013
Location: Australia
Online Status: Offline
Posts: 2
Quote Beej Replybullet Posted: 19 Dec 2013 at 11:52am
Thank you for your quick response Sastry, but I need to summarise the result of the above formula (count of UC's only) into a count of UC's under 4 days and count of UC's >= 4 days.  The variable formula isn't available in grouping and cannot be summarised within another formula "this field cannot be summarised". I feel it is something very simple, but is eluding me.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Dec 2013 at 4:07am
You won't be able to group or change your order on your resulting TotalUCDays. You are only able to get your datediff value because of the way the report is currently ordered. If you were to regroup it you would lose the ability to garner the data at all.
The best way to do what you are trying to do is to create a stored procedure (or possibly a crystal command) and do the datediff calculation there before you pull the data into crystal. It will then be available int he first pass of the report and can be used to group on or so other calculations.
Hopefully you have DB rights to create a stored proc a command or some equivalent.
IP IP Logged
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