topic Re: Return first data for last negative value period in QlikView

Return first data for last negative value period

Mon, 06 Oct 2014 12:41:45 GMT

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!

Ilgmar

Re: Return first data for last negative value period

anbu1984 — Mon, 06 Oct 2014 12:48:25 GMT

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

Mon, 06 Oct 2014 12:58:52 GMT

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

anbu1984 — Mon, 06 Oct 2014 13:00:50 GMT

Then how do you identify last negative value?

Re: Return first data for last negative value period

Mon, 06 Oct 2014 13:03:13 GMT

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

Mon, 06 Oct 2014 13:22:43 GMT

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

JonnyPoole — Mon, 06 Oct 2014 13:31:10 GMT

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;

Re: Return first data for last negative value period

anbu1984 — Mon, 06 Oct 2014 13:36:12 GMT

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

Mon, 06 Oct 2014 13:45:26 GMT

Thanks for advice!