Conditional Running Totals - Suppressing duplicates -URGENT!!

[ganjass]

Crystal V 8
Progress 9.1c

I have a report that is set up as so:

GP1: Date

GF:
months 1 - 12 across the report with sums of CPM01 to CPM012 formulae
the dates are based on the formula:

if Datediff("m",{@Placement Month Start},{debt-trans.tx-date})=0 and
{debt-trans.tran-code} startswith ["DR1","DR3","DR4","DR5","DR309"] then {debt-trans.tx-amount}
else
0

where the date diff goes from 0 to 12 for each month. the problem is that there has to be running totals for each month for the dates, but there are duplicates across the the months which are suppressed by the formulae, which run from 01 to 012:


{@%CPM02}={@%CPM01}
where %CPOM2 is :
if Sum ({@CPM01}, {debt.dt-datinstr}, "monthly&quot=0 or {#£Placements}=0 then 0 else
Sum ({@CPM01}, {debt.dt-datinstr}, "monthly&quot%{@NetNewBusiness}

etc across the months so that the report displays as a diagonal of sums per month ie the duplicates for a date are suppressed. The problem is not including the suppressed sums in the running totals i have tried a few things but i can't get it to work properly, i cant find a way to suppress the suppressed sums in the running total any ideas??

Thanks in advance

 

[synapsevampire]

You're best served to remove duplicates, if they're true duplicates, select Database->Select distinct records.

This is a tad consfusing:

{@%CPM02}={@%CPM01}

Why would they be the same?

I think that you're posting too much about how you're doing things and too little about the data and requirements. Consider that there might be another way were you to share that level of details.

-k

 

[ganjass]

i have already selected the distinct records option, although there are still, duplicates being repeated in the details section of the report.

depending on the diff between todays month and the start month of the record, a value of between 1 and 12 is associated with the month, so if the record has a diff value of 1 it should only have 1 entry in the report, but regardless of diff value it is repeating the 1 value for a diff value of 1 across all 12 fields, which is not good for the running totals, is there a way i can use arrays for this so that only so many values can be displayed according to the value of the diff. I hope this makes sense...

 

[synapsevampire]

There aren't duplicates.

It sounds like you either have a cartesian product, or you don't understand the data.

All of this text may prove helpful, but showing example data and expected output would be even more so. I really have little idea of what you're trying to accomplish, other than removing some values.

You probably need some sort of grouping formula, but I need more information.

Or you can use conditional running totals, just select the evaluate use a formula and place the criteria in there.

-k

 

[ganjass]

I understand what you are saying, and yes you are right i dont understand the data, as i am new and i'm the unenviable position of fixing reports that i'm not overly sure what they do! do you have an email that i could send you a copy of the report and an export of the data?

Cheers

 

[synapsevampire]

I'd prefer that you learn your requirements first, then post that information, if somone doesn't resolve it, I'll try to help directly.

Having the report won't help me understand what you want any better than you do now.

-k

 

[ganjass]

ok the report is a performance report that takes all new accounts into the report. It then groups the months of new accounts. the count of new accounts for that month as well as the gross and net value is then calculated for that month, no problem there. the report then takes the payments made on that account for each month since its start, using the date diff formula above. The purpose of this is to only show payments that make up the value of the diff ie if the account is 6 months old you get 6 months of payments etc. each field 1 to 12 has the date diff formula and a summary function based on it which gives all the payments for that month. the problem is when the diff goes past the number\ month it corresponds to, it duplicates the last value. This is easy enough to suppress but the duplicates affect the running totals. in essence i'm looking to stop the values after the datediff value, or saying if sum@cpm2 = sum@cpm01 ignore sum@cpm02 and all the values after it. I've tried conditional running totals by saying add the sums as long as it doesn't equal the last one, but no joy, this is fast becoming very frustrating! should any more infor be required let me know

1 2 3 4 5 6 7 8 9 10 11 12
Jul X X X X X X
Aug X X X X X
sep X X X X
Oct X X X
Nov X X
Dec X

 

[ganjass]

oh and forget the {@%CPM02}={@%CPM01} above in the initial post this was a mistimed paste

Cheers