Compare group totals
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=8146
Printed Date: 02 May 2024 at 2:16pm
Topic: Compare group totals
Posted By: Evil McBad
Subject: Compare group totals
Date Posted: 28 Oct 2009 at 2:35am
|
Hi - I have a crystal report which reports from a database which has new records added each month. The report is grouped, at the highest level, on 'Month' (this is a string field in the database - Eg "Aug", Sept" etc rather than a date field).
In the report, I have a field, say 'X', which is summed at group level. I wan't to compare this 'sumX' value for group 'September' with the same value for group 'August'.
I thought previous (sum({x},{month}) would be the answer, but I get the error "no previous or next value"
Any ideas, guys?
-------------
Evil
|
Replies:
Posted By: lockwelle
Date Posted: 28 Oct 2009 at 7:25am
|
use sum(x,{month}) and sum(x,{group})
As a thought, you might want to store the sum(x,{month}) in variable and then access that repeatedly...a little less on the calculating for CR.
HTH
|
Posted By: Evil McBad
Date Posted: 02 Nov 2009 at 2:58am
|
Probably being dense here, but not sure what you are advising - the month is the group, so unsure what you mean by sum x, month & sum x, group.
Again, apoloies if I have misunderstood.
-------------
Evil
|
Posted By: lockwelle
Date Posted: 02 Nov 2009 at 6:29am
|
OK, I thought you wanted something different. Do you always want to compare 2 consecutive periods? If so, variables are the way to go. you would need to set the 'last' period value at the time that you generate it, but you will need to have it lag, since you are also comparing at the same time. like:
shared numbervar lastMonth;
local numbervar thisMonth:=sum({table.field},{group});
shared numbervar diff;
diff:=lastMonth-thisMonth;
lastMonth:=thisMonth;
thisMonth;
now you can display the diff at some other location in the report.
This is the general idea, you might need to fiddle with it to get it to work exactly as you want, but you get the idea.
HTH
|
Posted By: Vertex
Date Posted: 02 Nov 2009 at 8:10am
IN THE BELOW CODE WHAT CODE IS USE FOR ALL RECORDS PARAMETER....
if {?Trial} ="This Month" then
{Order.CreateDateTime} in monthtodate
else if {?Trial} ="Last Month" then
{Order.CreateDateTime} in lastfullmonth
else if {?Trial} ="This Year" then
{Order.CreateDateTime} in yeartodate
else if {?Trial} ="Last Year" then
{Order.CreateDateTime} in lastyearYTD
else if {?Trial} ="All" then
....THANKS....
-------------
Hi Peoples
|
Posted By: DBlank
Date Posted: 02 Nov 2009 at 8:14am
|
if {?Trial} ="This Month" then
{Order.CreateDateTime} in monthtodate
else if {?Trial} ="Last Month" then
{Order.CreateDateTime} in lastfullmonth
else if {?Trial} ="This Year" then
{Order.CreateDateTime} in yeartodate
else if {?Trial} ="Last Year" then
{Order.CreateDateTime} in lastyearYTD
else {?Trial} ="All"
|
Posted By: Vertex
Date Posted: 02 Nov 2009 at 8:28am
FOR DISPLAYING DATE WITH RECORDS I USE TO FORMULA FROMDATE AND TODATE
HOW I CAN DISPLAY THE DATE FOR ALL RECORDS FOR BEGINNING DATE TO CURRENT DATE OF RECORDS...MY CODE IS HERE ....PLEASE GIVE ME CODE
else if{?Trial} = "This Week" then
( fromdate:=totext (dateadd('d',-weekday(currentdate),currentdate));
enddate:=totext (dateadd('d',-weekday(currentdate),currentdate)+7) )
else if {?Trial} = "Last week" then
( fromdate:=totext (dateadd('d',-weekday(currentdate),currentdate)-8);
enddate:=totext (dateadd('d',-weekday(currentdate),currentdate)-1) )
else if{?Trial} ="This Month" then
( fromdate:=totext(iStart);
enddate:= totext(DateAdd ('m',1,iDay-day(iDay)+ 1)-1) )
else if {?Trial} ="Last Month" then
( fromdate:=totext(iStart2);
enddate:= totext(DateAdd("d",-1,iStart)) )
else if {?Trial} ="ALL" then
.......THANKS/..
-------------
Hi Peoples
|
Posted By: Vertex
Date Posted: 02 Nov 2009 at 9:59am
FOR DISPLAYING DATE WITH RECORDS I USE TO FORMULA FROMDATE AND TODATE
HOW I CAN DISPLAY THE DATE FOR ALL RECORDS FOR BEGINNING DATE TO CURRENT DATE OF RECORDS...MY CODE IS HERE ....PLEASE GIVE ME CODE
else if{?Trial} = "This Week" then
( fromdate:=totext (dateadd('d',-weekday(currentdate),currentdate));
enddate:=totext (dateadd('d',-weekday(currentdate),currentdate)+7) )
else if {?Trial} = "Last week" then
( fromdate:=totext (dateadd('d',-weekday(currentdate),currentdate)-8);
enddate:=totext (dateadd('d',-weekday(currentdate),currentdate)-1) )
else if{?Trial} ="This Month" then
( fromdate:=totext(iStart);
enddate:= totext(DateAdd ('m',1,iDay-day(iDay)+ 1)-1) )
else if {?Trial} ="Last Month" then
( fromdate:=totext(iStart2);
enddate:= totext(DateAdd("d",-1,iStart)) )
else if {?Trial} ="ALL" then
.......THANKS/..
-------------
Hi Peoples
|
Posted By: DBlank
Date Posted: 02 Nov 2009 at 12:01pm
|
I already answered your question. You do not use the Fromdate or the todate. You just make it state {?Trial} ="ALL". This returns a TRUE value in the select expert. THis then has NO filtering on the DB which is equivalent to "all records".
|
Posted By: Vertex
Date Posted: 21 Nov 2009 at 2:02pm
for sum of records of this current whole month what to do
sum ({Order.OrderID}, {Order.CreateDateTime}, "daily")
-------------
Hi Peoples
|
Posted By: DBlank
Date Posted: 21 Nov 2009 at 3:55pm
|
You can create a formula field to determine if the field is in this month then SUM that formula field. Example formula field as 'InMonth':
if {Order.CreateDateTime} in monthtodate then table.sumfieldhere else 0
SUM( mailto:%7b@InMonth - {@InMonth })
|
|