Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Max Function in date Post Reply Post New Topic
Author Message
anushka
Newbie
Newbie
Avatar

Joined: 10 Apr 2008
Online Status: Offline
Posts: 29
Quote anushka Replybullet Topic: Max Function in date
    Posted: 17 Jul 2013 at 2:03pm
Hi ,
 
i am creating a report where i have to pull the dates for a type of document name. I created a formula in details section and then used max function in group to pull the expiration date of that document name as there are other document names too in that column.
 
However there are null values for few rows, now i have to do formating where all nul values will read "not available" but when i make my formula
 
if {table1.DOCUMENTNAME} = "physics" then
totext({table1.EXPIRATION_DATE},"MM-dd-yyyy")
else "MISSING"
 
but now all the values in that column are showing as missing because the max function at the group level think missing is greater than a date converted to text. Please advice if ther is a workwround to it.
 
Learning can be fun.
IP IP Logged
Sastry
Moderator
Moderator
Avatar

Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
Quote Sastry Replybullet Posted: 18 Jul 2013 at 1:19am
Hi

Use your formula for display purpose and do not insert Max() on your formula.

Insert Max() on date and place that in your gorup footer.

i.e. place your formula in detail to show null values as 'Missing', but while inserting maximum date in group footer level use your date only.

or

create a formula to insert max date in group footer like ..

Maximum({table1.EXPIRATION_DATE},Groupname)

Thanks,
Sastry
IP IP Logged
anushka
Newbie
Newbie
Avatar

Joined: 10 Apr 2008
Online Status: Offline
Posts: 29
Quote anushka Replybullet Posted: 18 Jul 2013 at 7:23am
Hi , Thanks for the response but doesnt seem to work. So here is what i did after you suggested.
 
Detial Sections
 
Created a formula called FLAG EXPIRE DT
if {table1.DOCUMENTNAME} = "physics" then
totext({table1.EXPIRATION_DATE},"MM-dd-yyyy")
else "Missing"
 
Group header
 
I used the max function for variable " FLAG EXPIRE DT"  as the variable has the condition for which document i need dates
 
Now when i run report i get only missing values.
 
If i change the formula to
if {table1.DOCUMENTNAME} = "physics" then
totext({table1.EXPIRATION_DATE},"MM-dd-yyyy")
then i get dates and null values. These nulll values should be flagged but i dont know how ?
Learning can be fun.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 18 Jul 2013 at 7:37am
I think Sastry just missed the part where you max value has a condition on it.
there are several ways to do this.
one way would be this...
change your formula to
if {table1.DOCUMENTNAME} = "physics" then {table1.EXPIRATION_DATE} else date(1900,1,1)
 
now you can do a max value of this for this formula field at your group header
right click on the formula field
select format field
select common tab
select display string
insert the formula
if currentfieldvalue=date(1900,1,1) then 'Missing' else totext(currentfieldvalue,'MM-dd-yyyy')
do the same thing for your summarized field
 
 


Edited by DBlank - 18 Jul 2013 at 7:44am
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.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.