A way that I can count certain records twice? 1

[southbean]

Hello All,

I’m trying to work out a way that I can count certain records twice.

What I’m trying to do in the formula below is this:

If {table.created_date} in {@StartDate} to {@EndDate} Then "New" Else 
If {table.completed_date} IN YearToDate Then "Completed YTD"

I have a weekly report where I need to count the number of records that have been completed in the current year and the number of ‘new’ requests during the past week.

The problem is that some of the ‘completed’ records can also ‘new’ records (created & completed in the same week) and thus my sums are off (the ‘new’ record total are deducted from the corresponding ‘completed’ record total).

Does anyone have guidance on how to do this?


Thanks for any/all help with this!!!

-tm

 

[synapsevampire]

Don't use an ELSE...

whileprintingrecords;
numbervar New;
numbervar Comp;
If {table.created_date} in {@StartDate} to {@EndDate} Then
New:=New+1;
If {table.completed_date} IN YearToDate Then
Comp:=Comp+1

Now later you can isplay each value;

whileprintingrecords;
numbervar New;

whileprintingrecords;
numbervar Comp;

-k

 

[southbean]

Hi synapsevampire,

Thanks for your reply!

It looks good and I tried it. However - I should have mentioned this before - this is for a cross-tab report and it doesn't appear I can use this type of formula in a cross-tab.

Is that right?

Thanks again!

- Tom

 

[Madawc]

I think a crosstab will only count something once. You'll need to create a 'Mock Crosstab'.

A 'Mock Crosstab' is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then.

Crystal should have included an example along with the Crosstabs. You can save a little time by doing a paste to a dummy report, changing the name and then pasting back.
Each running total will count the record if it was within the criteria - in your case, date range and year to date.

Depending on what you are doing, it might also be possible to do it by running totals placed in groups, with details suppressed and just the group footer shown.

It helps to give your Crystal version - 8, 8.5, 9, 10, 11 or whatever. Methods sometimes change between versions, and higher versions have extra options.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Just make separate formulas like this:

//{@new}:
if {table.created_date} in {@StartDate} to {@EndDate} then 1

//{@completed}:
If {table.completed_date} in YearToDate Then 1

You can use these in a crosstab.

-LB

 

[southbean]

Hello All,

Thanks for ALL your replies!

I’m still struggling with this cross-tab. I’m thinking it may not be possible. Here’s my formula:

IF {table1.created_dt} IN {@StartDate} TO {@EndDate} THEN "New" ELSE 
IF {table1.status_cd} = "ACTV" THEN "Active" ELSE
IF {table1.status_cd} = "ASGN" THEN "Assigned" ELSE
IF ({table1.status_cd} = "CMPL" AND 
{table1.completed_dt} IN YearToDate) THEN "Completed YTD" ELSE
IF ({table1.status_cd} = "CANC" AND
{table1.completed_dt} IN YearToDate) THEN "Cancelled YTD"

What I’m trying to do with this formula is create columns of ‘New’, ‘Active’, ‘Assigned’, etc. I’m using the record ID to count in the summarize section in the cross-tab.

The problem is that some of the ‘Active’, ‘Assigned’, ‘Completed YTD’ etc., records can also be ‘New’ records (created & completed or in ‘Active’ status in the same week) and thus my sums are off (the ‘New’ record total are deducted from the corresponding ‘Completed YTD’, ‘Active’ etc. record totals).

Is a loop required to re-count the ‘New’ records?

Again, thanks to all for your replies and for any further guidance you can provide!!!

- tm

 

[southbean]

Hello All,

Never mind. I didn't quite get what lbass was getting at, but she/he was spot on.

I created a formula for each, put them all in the summary section and then made the summary fields 'Horizontal'.

Worked like a charm.

Thanks for ALL your help (and patience)!!!

- tm