Calculations 1

[ganjass]

CR10
Progress

Hi ALL

I have a problem conditionally adding totals in a report. The group consists of group of a date displayed by month. I have created a counter in the group due the fact that you aren't always gauranteed to get concurrent months back, so datdiffs and counts of CDate isn't happening.

The issue is if there are 13 months brought back the 13th is ignored and the next,3,6,9,and 12 month cumulatives are calculated. The 13th month would be the most recent ie would be number 13, so i am using

whileprintingrecords;
numbervar Gross1;
numbervar counter;

if counter in [10 to 12]
Then


If (OnFirstRecord or {debt.debt-code} <> Previous({debt.debt-code})) then
Gross1 := Gross1 + {debt.dt-debtval}

else
Gross1 := Gross1

This works fineif there are 13 months, but if there are say only 11, or 10 months brought back this throws the calulation out of wack. You cant use maximum with the counter and i've tried counter -3 to counter but no joy any ideas

Thanks in advance

 

[synapsevampire]

Pretty confusing post.

Rather than lengthy text descriptions, try providing example data and expected output.

-k

 

[ganjass]

well synapse, the data is as so

Date month Net Amount Counter
..
..
OCT 1000 8
NOV 2500 9
DEC 2500 10
JAN 2500 11
etc

ok so in the above example, i would have

Totals as so

3months 7500 (Sum Net amount when counter 9 to 11)
6months etc (Sum Net amount when counter 6 to 11)
9months etc (Sum Net amount when counter 3 to 11)
12months etc (Sum Net amount when counter 1 to 11)

the issue i have making the the sums dynamic so when there are 13 months, which is the max number of months, the 3 months calculation would be Sum Net amount when counter 10 t0 12 (as the 13th month has to be ignored) , if there where 12 months then the 3months would be the Sum Net amount when counter 10 to 12, etc,

Does this make more sense

 

[lbass]

You might try using logic like the following, where {?date} is a date range parameter:

//{@3months}:
whileprintingrecords;
numbervar monthno := datediff("m",minimum({?date}),maximum({?date}));
numbervar gross1;

if monthno = 13 then
(if {table.date} in dateadd("m",-3, date(year(maximum({?date})),month(maximum({?date})), 01)) to
date(year(maximum({?date})),month(maximum({?date})), 01)-1 then
If (OnFirstRecord or
{debt.debt-code} <> Previous({debt.debt-code} )) then
Gross1 := Gross1 + {debt.dt-debtval} else
Gross1 := Gross1)

else
if monthno in 3 to 12 then
(if {table.date} in dateadd("m",-2, date(year(maximum({?date})),month(maximum({?date})), 01)) to
dateadd("m",1,date(year(maximum({?date})),month(maximum({?date})), 01))-1 Then
If (OnFirstRecord or {debt.debt-code} <> Previous({debt.debt-code} )) then
Gross1 := Gross1 + {debt.dt-debtval} else
Gross1 := Gross1)

else
if monthno < 3 then
(if {table.date} <= dateadd("m",1,date(year(maximum({?date})),month(maximum({?date})), 01))-1 Then
If (OnFirstRecord or {debt.debt-code} <> Previous({debt.debt-code} )) then
Gross1 := Gross1 + {debt.dt-debtval} else
Gross1 := Gross1);

I'm not clear on your report structure, but you might be able to adapt something like this.

-LB