Return first data for last negative value period

[#1]

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

Thanks for advice!

BR

Ilgmar

[anbu1984]

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

[#3]

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

[anbu1984]

Then how do you identify last negative value?

[#5]

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?

[#6]

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.

[JonnyPoole · Accepted solution]

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))

Load Script:

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:

load

*,

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

LOAD

  *,

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

Resident Temp

Order by RecordNumber desc;

drop table Temp;

[anbu1984]

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

];

[#9]

Thanks for advice!