Print Page | Close Window

Last Date/Time of previous month

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=20956
Printed Date: 19 May 2024 at 1:26am


Topic: Last Date/Time of previous month
Posted By: Dr4ke
Subject: Last Date/Time of previous month
Date Posted: 08 Aug 2014 at 2:40am
Afternoon all,

I currently have the below fields in my Crystal Report (crosstab). What I am trying to do is set the criteria so that only the very last date/time combination is shown from last month.

Date&Time (Row)
ComputerName (Column)
DiskID (Column)
Avg of DiskSizeMB (Summarized)
Avf of DiskUsedPct (Summarized)

What I thought would work is the below formula but unfortunately this doesn't work due to an error ("This function cannot be used because it must be evaluated later.").  Is there another way to achieve this??

{Table1.DATE_TIME} in LastFullMonth and
{Table1.DATE_TIME} = Maximum({Table1.DATE_TIME})


Replies:
Posted By: lockwelle
Date Posted: 08 Aug 2014 at 5:07am
why not a formula like:
local datetimevar nmonth = dateadd("month",1,{table.field});
dateadd("d",-1,date(year(nmonth),month(nmonth),1));

this will always give the last day of the month.
first we add a month to the date, then we create a date that is the first of that month(the next month)
then we subtract 1 day, which is the last day of the month.

This does not need to be evaluated later, and should work for you to summarize off of.

HTH

Posted By: Dr4ke
Date Posted: 08 Aug 2014 at 5:24am
Hi Lockwelle,

Unfortunately the data I am using is collected every 15 minutes so I have to get the last Date and also the last Time of the Date/Time field so must be summarised.

I may have found the solution with the following link: I am just testing to make sure it all works...

%20http://www.maximumimpactsolutions.co.uk/blog/comments.asp?bd=129%20 - http://www.maximumimpactsolutions.co.uk/blog/comments.asp?bd=129

Posted By: Dr4ke
Date Posted: 08 Aug 2014 at 5:31am
I can confirm that this has provided what was required.


Print Page | Close Window