Crystal Reports Formula field

Technical Questions

Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions

Topic: Formula field

Author	Message
Prenika Newbie Joined: 11 Mar 2009 Location: India Online Status: Offline Posts: 8	Topic: Formula field Posted: 17 Mar 2009 at 10:19pm
	I am using Crystal report XI,I have designed a report that has to be run on half yearly basis n based on that i have to do a count for some records but there is an error in the formula that i have written if({Command.DATE})<=Date (Year(CurrentDate),06 , 30) then Count ({Command.TICOUNT}) else if({Command.DATE}>=Date (Year(CurrentDate),06 , 30) and {Command.ISSUEDDATE}<=Date (Year(CurrentDate),12 , 31) ) then Count ({Command.TICOUNT}) It wqould be very helpful if someone could reply at the earliest
	IP Logged


DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 18 Mar 2009 at 7:13am
	Not sure what you are trying to get from this formula exactly...Are you trying to get to distinct counts based on the date of a record (Command.Date field)? You can do this by doing 2 running totals: First running total called "JanToJune" Field to Summarize = {Command.TICOUNT} Type of Summary=Count Evaluate as "Use a Formula": ({Command.DATE})<=Date (Year(CurrentDate),06 , 30) Reset on NEVER (assuming you want this for all records in the report) Create a second running total as "JulyToDec" and repeat the steps but change the formula to your second condition
	IP Logged

Prenika Newbie Joined: 11 Mar 2009 Location: India Online Status: Offline Posts: 8	Posted: 18 Mar 2009 at 9:13pm
	i am trying to get the distinct count based on the date field on a half yearly basis,will try out your suggestion.thanks
	IP Logged

Prenika Newbie Joined: 11 Mar 2009 Location: India Online Status: Offline Posts: 8	Posted: 18 Mar 2009 at 9:49pm
	though there are 4 records it is giving the count as 0.did the same way as u had suggested
	IP Logged

Prenika Newbie Joined: 11 Mar 2009 Location: India Online Status: Offline Posts: 8	Posted: 19 Mar 2009 at 2:55am
	This is the what i am using in the formula field: if({Command.ISSUEDATE}) in YearToDate then Sum ({Command.PMNTFHCA}) ({Command.ISSUEDATE}) - this field is a datetime field is it appropriate to use this in the above condition I want to do the summation of a particular field based on the date condition.As it is a yearly report i have given the YearToDate condition. But i am not getting the value for this formula Then i tried without the date condition gave only Sum ({Command.PMNTFHCA}) in the formula field then the value is shown. The date condition is required n i tried the running total too as u had suggested but it dint work Edited by Prenika - 19 Mar 2009 at 3:24am
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 19 Mar 2009 at 7:01am
	Hi Prenika, Still not exactly sure what you are going for but will try to help further. If you want a sum of {Command.PMNTFHCA} for the year you can either do it as a running total or create a formula field to handle it then sum on that. FOr the axample I will use a formula called "YTD". Create the formula as: if({Command.ISSUEDATE}) in YearToDate then {Command.PMNTFHCA} else 0. Now do a Sum on the formula field. SUM({@ytd}) For you other problem spliting it into two part of the year and counting the records you can use formulas as well. Note this willa ct like a record count and not a DistinctCount. For Jan-Jun if({Command.DATE})<=Date (Year(CurrentDate),06 , 30) then 1 else 0 Sum this formula field. For Jul-Dec if({Command.DATE}>=Date (Year(CurrentDate),06 , 30) and {Command.ISSUEDDATE}<=Date (Year(CurrentDate),12 , 31) ) then 1 else 0 Sum this formula field. FYI in running totals formulas don't use an If then statment or summary, just identify the condition that you want it included and let the process you pick in the upper portion do the SUm, COunt, DistinctCOunt, etc. Edited by DBlank - 19 Mar 2009 at 7:01am
	IP Logged

Prenika Newbie Joined: 11 Mar 2009 Location: India Online Status: Offline Posts: 8	Posted: 19 Mar 2009 at 11:05pm
	I have just started working with crystal reports XI n dint know much about that,thanks a lot for the detailed explanation
	IP Logged

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