Return the year with a <0 value and the previous year and value 1

[mmck1964]

I have a report grouped by account number and I am trying to return any years with an amount value less than zero, and return the previous year and value. I can get the the year and value less than zero, but I need help in bringing back the year prior to the year with the negative value and the value for that year.
In the example below, I would like to return 1982 -1.05 and 1981 9.66.

Example:
Account Number Year Value
12345
1984 8.65
1983 5.22
1982 -1.05
1981 9.66
1980 7.35

Tahnk you for any help.

 

[IanWaterman]

Split Year group footer displaying sum() into two sections leaving the sum() in top section

Create a formula, place in top section and suppress
@eval
whileprintingrecords;

Global booleanvar year;

If sum(amountField, datefield, "yearly") < 0 then year:=true // assuming you have grouped on a date filed and then selected yearly. If you have a Year field then just use sum(amountField, yearfield)
In bottom section place this formula
@reset
whileprintingrecords;

Global booleanvar year:=false

In section expert suppress formual for top section

whileprintingrecords;
Global booleanvar year;

sum(amountField, datefield, "yearly") > 0 and year = false

Suppress the lower section

That should just show 1982 and 81

Ian

 

[mmck1964]

Sorry the example did not display as I had typed. Below is what I entered based on the reply:
GH1 is grouped by Account
I added and suppressed {f_Eval} which has:
WhilePrintingRecords;
Global Booleanvar Year;
If sum({CC_PY_BY_TYPE.CC_UNRET_AMT}, {CC_PY_BY_TYPE.CC_YR}) < 0 then year:=true

GH2 is grouped by {CC_PY_BY_TYPE.CC_YR} and nothing in the group

Detail Section:
[{CC_PY_BY_TYPE.CC_YR}] [{CC_PY_BY_TYPE.CC_UNRET_AMT}] [{f_Tr_Amt}]
In [{f_Tr_Amt}] I have:
whileprintingrecords;
Global booleanvar year;
sum({CC_PY_BY_TYPE.CC_UNRET_AMT}, {CC_PY_BY_TYPE.CC_YR}) > 0 and year = false

GF2 nothing in this group

GF1
{f_Reset} which is supressed
whileprintingrecords;
Global booleanvar year;
sum({CC_PY_BY_TYPE.CC_UNRET_AMT}, {CC_PY_BY_TYPE.CC_YR}) > 0 and year = false

I am sure I misinterpreted what your posted, but what I get in the detail section is

1984 8.65 True
1983 5.22 True
1982 -1.05 False
1981 9.66 True
1980 7.35 True

 

[IanWaterman]

You say you have nothing in GH2 and GF2, surely these are the years and will show your totals

1984 8.65 True
1983 5.22 True
1982 -1.05 False
1981 9.66 True
1980 7.35 True

{f_Eval} should be in GH2 and header suppressed

This is not a formula, it is a boolean condition which should be in the section ssuppression formula for GF2, which I assumed had your year nad subtotal

whileprintingrecords;
Global booleanvar year;
sum({CC_PY_BY_TYPE.CC_UNRET_AMT}, {CC_PY_BY_TYPE.CC_YR}) > 0 and year = false

GF2 should be split in two and the lower section should be suppressed and the formula
{f_Reset} placed there.

Ian

 

[IanWaterman]

If your detail data is the individula years then process is a lot easier. I assumed you were summarising detailed data to give you a sum for the year.

If detailed then you can suppress easily.

In detail section expert in the suppression formula box

If not onfirstrecord then
(IF {CC_PY_BY_TYPE.CC_UNRET_AMT} < 0 then false
else
If previous({CC_PY_BY_TYPE.CC_UNRET_AMT}) <0 then false
else true
)
else false

Ian

 

[mmck1964]

Actually all I want to return in the detail section is the year with a negative value and the negative value and the previous year and its value.

Below is what I would like to return based on the example:

1983 5.22
1982 -1.05

 

[IanWaterman]

Provided you have you data sorted in Year descending order then chnage to

If not onfirstrecord then
(IF {CC_PY_BY_TYPE.CC_UNRET_AMT} < 0 then false
else
If Next({CC_PY_BY_TYPE.CC_UNRET_AMT}) <0 then false
else true
)
else false


Ian