Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Datediff question Post Reply Post New Topic
Author Message
Needhelp
Newbie
Newbie
Avatar

Joined: 30 Nov 2012
Online Status: Offline
Posts: 13
Quote Needhelp Replybullet Topic: Datediff question
     Posted: 25 Feb 2013 at 8:35am
Hi

Am using CR X1

I have been trying to crate a formula that will group people by attendance.

Group 1 is those people who have newly arrived in the past 2 months.

Group 2 is people who have attended in the last 2 months and who have attended in the previous 10 months.

Group 3 are people who have attended in the past 2 months but who previously attended over a year ago (but did not come in months 2 - 12).

Group 4 is people who have not attended in over a year.

I can calculate datedifference for each time the person has attended but have really struggled to find the correct syntax to identify group 3.

Does anyone have any ideas how I can identify these people?

Edited by Needhelp - 25 Feb 2013 at 8:35am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Feb 2013 at 8:55am
are you able to use s stored proc or command as your source?
IP IP Logged
Needhelp
Newbie
Newbie
Avatar

Joined: 30 Nov 2012
Online Status: Offline
Posts: 13
Quote Needhelp Replybullet Posted: 25 Feb 2013 at 9:09am

No - I am afraid not. Is there a way round it?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Feb 2013 at 9:46am
do you need it actually grouped or do you just need counts?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Feb 2013 at 9:46am
i fyou need it grouped I think you might end up with 4 sub reports
IP IP Logged
Needhelp
Newbie
Newbie
Avatar

Joined: 30 Nov 2012
Online Status: Offline
Posts: 13
Quote Needhelp Replybullet Posted: 25 Feb 2013 at 10:01am
Grouped with a count but can't figure out how to crate teh 3rd sub report. I did think I might need to do sub reports.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Feb 2013 at 10:36am
for each sub report you will need to start with your full data set
create 3 formulas, 1 per criteria you have.
I will assume you want these criteria based on today's date
 
//current
if date > dateadd('m',-2,currentdate) then 1 else 0
 
//mid
if date in dateadd('m',-12,currentdate) and dateadd('m',-2,currentdate) then 1 else 0
 
//old
if date < dateadd('m',-12,currentdate) then 1 else 0
 
group by client and sum each of these formulas by client
 
Now you can do group selection (in each sub report) based on the specific criteria
 
group 3 would be
sum(current,client)>0 and sum(mid,client)=0 and sum(old,client)>0
IP IP Logged
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