Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Formula Editor Post Reply Post New Topic
Page  of 2 Next >>
Author Message
billbjr
Newbie
Newbie


Joined: 19 Feb 2009
Location: United States
Online Status: Offline
Posts: 14
Quote billbjr Replybullet Topic: Formula Editor
     Posted: 26 Feb 2009 at 1:47pm

I'm trying to write a simple query for two tables that I have, but I'm not having a lot of luck. I want the query to sum the theoretical win (Twin) for a player if they have activity in the last 6 months. I wrote a script for it but it's not working:

 

Sum ({CDS_STATDAY.TWin}) if
    {CDS_STATDAY.GamingDate} is >= (CurrentDate-180)

 

It's not recognizing anything after: Sum ({CDS_STATDAY.TWin}) . I guess that means the “if” is wrong. If so, then what do I use?

 

Help
Bill
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 27 Feb 2009 at 1:17am
Hi Bill
 
Change the code to
 
if {CDS_STATDAY.GamingDate} >= CurrentDate-180 then
    Sum ({CDS_STATDAY.TWin}) 
 
Cheers
Rahul
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Feb 2009 at 5:58am
Note the above will only sum instances where the date meets the 180 day condition. I think you want all records summed if any date is > the180 criteria. If that is the case you may want to clarify.
IP IP Logged
billbjr
Newbie
Newbie


Joined: 19 Feb 2009
Location: United States
Online Status: Offline
Posts: 14
Quote billbjr Replybullet Posted: 27 Feb 2009 at 8:05am
yes, I do want ALL records summed if any date is >=currentdate-180, not just the records that equal currentdate-180. How would you do that?
Bill
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Feb 2009 at 8:08am
Are you working in SQL and can you create and use views?
Also do you need to display all "players" regardless of the 180 day criteria or do you want to display only users where the 180 criteria is met?


Edited by DBlank - 27 Feb 2009 at 8:12am
IP IP Logged
billbjr
Newbie
Newbie


Joined: 19 Feb 2009
Location: United States
Online Status: Offline
Posts: 14
Quote billbjr Replybullet Posted: 27 Feb 2009 at 8:15am
No, I am using Crystal's formula editor. I do not have SQL.
 
Yes, I need to display all players regardless if they meet the 180-day criteria
Bill
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Feb 2009 at 8:35am

OK, you need to create a process to 'flag' the players you want summed.

Group on the player name.
Create a formula for yuour flag as "CountFlag":
if datediff("d",currentdate,{CDS_STATDAY.GamingDate})<180 then 1 else 0
 
Place this on your details line and use the summary function to create a SUM of this field on Group 1 (player name group)
Place this summary field in your group 1 header. Now any player that had a date in the last 180 days has a sum >0 and everyone else has a sum=0.
Now do a running total.
Call it "SumofFlagedPlayers"
Field to Summarize ={CDS_STATDAY.TWin}
type of Summary=SUM
Evaluate as a formula: Sum ({@CountFlag}, {table.playername})>0
//this is the summaryfield in your group1
Reset as On change of Group and set it to group1
Place this running total in group footer1
//running totals only work in footers not headers
 
you can suppress the Summary and the formula field so they don't apear in your report view
IP IP Logged
billbjr
Newbie
Newbie


Joined: 19 Feb 2009
Location: United States
Online Status: Offline
Posts: 14
Quote billbjr Replybullet Posted: 27 Feb 2009 at 11:16am

i set up everything like you said, but it's summing the Twin for records BEFORE six months ago. for example, if a player had two trips, one on 3/15/08 for $10.00 and one on 1/23/09 for $10.00, it should only give me the sum for the one trip on 1/23/09 ($10) because that's the only record within 180 days. But instead it's adding both trips together, like it's ignoring the 180 day criteria.
Bill
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 27 Feb 2009 at 11:30am
Hi Bill,
I am a little confused. Earlier I asked this question and you responded
"yes, I do want ALL records summed if any date is >=currentdate-180, not just the records that equal currentdate-180. How would you do that?".
Now you are stating that you only want the records where the date is >=currentdate-180.
I apologize as much of the above is not needed to do that and this is why I was asking specifically what you wanted. Rahul's original post was mostly adequate for that.
You can also easily address this by doing another running total call it something like "Last180sum":
Field to Summarize ={CDS_STATDAY.TWin}
type of Summary=SUM
Evaluate as a formula: datediff("d",currentdate,{CDS_STATDAY.GamingDate})<180
Reset as On change of Group and set it to group1
Place this running total in group footer1
 
 
Or if you want to use the SUmmary function instead create a formula field called something like "Last180only":
fomrula is:
if datediff("d",currentdate,{CDS_STATDAY.GamingDate})<180 then {CDS_STATDAY.TWin} else 0
// This will convert your data to only giving values if the date was in the last 179 days.
Do a summary finction SUM on this formula field and at grouplevel1 (playername)
 
 
IP IP Logged
billbjr
Newbie
Newbie


Joined: 19 Feb 2009
Location: United States
Online Status: Offline
Posts: 14
Quote billbjr Replybullet Posted: 27 Feb 2009 at 11:52am
sorry for the mistake. I should have said "yes, I do want ALL records summed if THE date is >=currentdate-180"
 
Anyway, I tried both of your suggestions above and they're close, but not exact. the first suggestion still sums ALL of the records for a player, even if a record falls outside of the 180-day parameter.
 
The second suggestion returns the TWin for just the last record for a player, regardless if it is in the 180-day parameter.
Bill
IP IP Logged
Page  of 2 Next >>
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.