8 Replies Latest reply: Oct 6, 2014 9:45 AM by ilgmars dakulis

# Return first data for last negative value period

Hello,

I need return first Date for last negative Value period.

Table:

INLINE [ID, Date, Value

1, 1/1/2014, -3

1, 1/2/2014, -4

1, 1/3/2014, -9

1, 1/4/2014, -7

1, 1/5/2014, 6

1, 1/6/2014, 4

1, 1/7/2014, -5

1, 1/8/2014, -3

1, 1/9/2014, -7

];

Need add field with marked 1/7/2014 for ID=1

BR

Ilgmar

• ###### Re: Return first data for last negative value period

How do you define last negative value? Do you select the row followed by last positive value(Here 4)?

• ###### Re: Return first data for last negative value period

not quite, becouse some ID can start with negative value and stay negative whole period, then it does not work.

• ###### Re: Return first data for last negative value period

Then how do you identify last negative value?

• ###### Re: Return first data for last negative value period

Definition:

if last report date ID value is negative, we need return data, when started last negative period. (In real, when it became as bad debitor)

Is it clear?

• ###### Re: Return first data for last negative value period

As I understand correctly, you need to add a flag to your table.

Your definition seems to be wrong what you need the result on the test data described above.

Please post some more data with test cases to understand the requirement clearly.

• ###### Re: Return first data for last negative value period

So if your data doesn't have positive values, then you need Date - 1/1/2014?

Table:

INLINE [ID, Date, Value

1, 1/1/2014, -3

1, 1/2/2014, -4

1, 1/3/2014, -9

1, 1/4/2014, -7

1, 1/7/2014, -5

1, 1/8/2014, -3

1, 1/9/2014, -7

];

• ###### Re: Return first data for last negative value period

Used some sorting in the load script to pick up the first negative value in the latest continous block of negative values:

List box expression:

=FirstSortedValue( total <ID> Value,if(Flag=1,-RecordNumber))

Chart expression:

=FirstSortedValue(TOTAL <ID> Value,if(Flag=1,-RecordNumber))

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='\$#,##0.00;(\$#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Temp:

*,

RecNo() as RecordNumber,

if(Value < 0 and Previous(Value) < 0 , RecNo()-1,RecNo()) as [12Negative]

INLINE [ID, Date, Value

1, 1/1/2014, -3

1, 1/2/2014, -4

1, 1/3/2014, -9

1, 1/4/2014, -7

1, 1/5/2014, 6

1, 1/6/2014, 4

1, 1/7/2014, -5

1, 1/8/2014, -3

1, 1/9/2014, -7

];

NoConcatenate

*,

if([12Negative]=Previous([12Negative]),1,0) as Flag

Resident Temp

Order by RecordNumber desc;

drop table Temp;