Dynamic Arrays and Group 1

[ganjass]

Hi All,

CR8 Progress 9.1c.

I have a lil quandry on my hands and i was wondering if anyone could help.

I have a report that has one group for (current date -380 days)displayed as month and year with the summery totals for date diffs running along the top from 1 to 12. The formula of these are as follows where the values go from 0 to 12 for each of the columns 1 to 12 as so below:

Date 0 1 2 3 4 5 6 7 8 9 10 11 12
Month1 X X X X X X X X X X X X X
Month2 X X X X X X X X X X X X
Month3 X X X X X X X X X X X
Month4 X X X X X X X X X X
etc.

placement month Start: Date(year(d.date)month(d.date),1)

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

the problem is the values that i need run across the columns 0 to 12 but then they duplicate and throw all the totals out of tilt what i need is for arrays to be used for each month dtynamically populated so as to remove duplicates and keep everything under control.

Thanks in advance

 

[synapsevampire]

You need to define what a duplicate is.

How you handle dupes is Crystal version dependent, which you didn't share. Try Database->Select Distinct Records

And why are there months on the X and Y? Are they different fields?

Otherwise it may be table join related or design in general.

Your formulas seem well designed to provide uniqueness per month for one month field, but what's in the other axis, was it supposed to be year, or?

Rather than supplying a mock of output only, try posting example data and expected output (not X's).

-k

 

[lbass]

I see this is a problem you have posted before. I think what would help to get useful responses is if you posted a sample of results at the detail level with actual numbers so we can see the duplication you are referring to. Show us the group level also.

I suspect that this problem results from the joins between the two tables referenced in your formulas: Table d and Table debt-trans. If you are joining by a customer number, for example, then every table d record will be repeated for every debt-trans record per customer. If, however, you joined on two fields, the customer number and a transaction field, for example, you might eliminate the duplicates. But you'd have to understand the database fields well enough to know what fields to join. Looking at the detail level of the report can help with this. I would try adding unique ID fields that identify specific records in order to determine where the repetition is coming in.

-LB

 

[ganjass]

Hi,

Synapse the select distinct records doesn't hit the spot with this issue, and the version is CR8 as i stated in the inital problem statement. Lbass your right i posted this before and being new to this job and not knowing the quirks and complxities of the database have added to my headache, especially when there is a large dollop of stress in the mix as well. The values along the top are sums of the date diff formula, where the date diff goes from 0 to 11 for the first 11 sums for 11 months and then:

if {debt-trans.tran-code} startswith ["DR1","DR3","DR4","DR5","DR309"] then {debt-trans.tx-amount}
else
0

for month 12.

The purpose of this is to have the performance of these accounts per month for each number of full months from the current date eg the sum of the tx-amount for each month that corresponds to the value of the date diff ie 6 date diff 6 gives 6 months sums of tx-amounts:

0 1 2 3 4 5 6
Jul-03 177.67 537.84 937.12 1757.48 2346.77 2642.15 2642.15
Aug-03 33.00 257.10 284.10 284.10 284.10
Sep-03 20.00 44.00 44.00 44.00 44.00
Oct-03 10 110.00 110.00 110.00 110.00 110.00 110.00
Nov-03 15 105 105 105 105 105 105
Dec-03 10 10 10 10 10 10 10

The report is created as so:

GH Date
GF1a Sums of date diff formulae for each momth
RF Totals For each month (which are out of whack)

Another problem is also:

if there are no DR codes in a particular month, the report will leave the column blank rather than taking a running total or the total paid for that batch. Similarly in month 1 if there are no collections it should say 0 and until the first month there is a payment, but again it remains blank.

Hope this helps

 

[lbass]

First you have to deal with your nulls. For your monthly formulas, you might change them to something like the following for datediff = 2:

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

I'm guessing that at the detail level, your formulas are accumulating values from previous columns as in:

if not isnull({debt-trans.tran-code}) and
Datediff(&quot;m&quot;,{@Placement Month Start},{debt-trans.tx-date})<= 2 and //note that this includes values 0 to 2
{debt-trans.tran-code} startswith [&quot;DR1&quot;,&quot;DR3&quot;,&quot;DR4&quot;,&quot;DR5&quot;,&quot;DR309&quot;] then
{debt-trans.tx-amount}
else
0

Instead use a formula like my earlier one which is limited to one datediff result (e.g., datediff = 2) and then use summary formulas in the
group footer like the following for the datediff = 2 column,e g.:

if sum({@datediff2},{table.group}) = 0 then 0 else
sum({@datediff0},{table.group}) + sum({@datediff1},{table.group})+ sum({@datediff2},{table.group})

This formula will prevent the repetition of the final sum across the datediff columns --which as I understand it is the duplication problem you are having.

For the Report Footer, change the formulas to the following (using datediff2 column as an example):

if sum({@datediff2}) = 0 then 0 else
sum({@datediff0}) + sum({@datediff1})+ sum({@datediff2})

-LB

 

[ganjass]

That was a sound answer there lBass, and it was looking like the solution. Until i was told it should be accumulative. So the brief is that it should be accumulative until the date diffis actually greater than the diff of the 2 dates.

 

[lbass]

Actually, that's what my suggestion does--at the group and report level--it accumulates the sums until the data does not meet the column criteria and then it returns zero.

If you want further help, you really need to provide specifics of your data and the contents of all your formulas--we still have never seen the formula used for the group summaries, for example, and I think this is where your problem lies. Most of what I offered was based on a lot of guesswork and from reading your former post. When someone gives you a suggestion you need to be specific about how it does not work for you, so respondents can use this feedback to shape their suggestions. In other words, make it easy for people to help you.

So let me ask--when you implemented my suggestion, what did your resulting data look like, and how was it different from what you wanted?

-LB

 

[ganjass]

Apologies Lbass, too much stress has been affecting my IQ the accumulation is now working a lot better, thanks for that, although there is one little issue. For most part this solution works well. But the requirement is for the columns to be like running totals, until the date diff is reached and then it should ignore everything else ie replace with zero's which for most part this does but there are occurencies where this does not happen. the brief is then that ie

date diff: 5
1 2 3 4 5
sep-03 0 63.3 0 0 0

what the report should do is

sep-03 0 63.3 63.3 63.3 63.3

until there is a new batch it should repeat until the datediff is reached.

I've tried saying if date diff is less that the column number then 0 else if the sum for that month is zero then print the previous datediff sum, else total all the previous sums. but no joy!