Announcements
[WEBINAR] Accenture & Qlik: Accelerating BI Migration to SaaS with Qlik on Dec 13th: REGISTER
cancel
Showing results for
Did you mean:
Partner - Creator II

Display Date when Value become negative

Hi, I have this table,

 ACCOUNT DATE AMOUNT 101 1/2/2020 -500 101 1/17/2020 1000 101 2/25/2020 -200 101 2/29/2020 300 102 2/9/2020 600 102 2/11/2020 700 102 3/21/2020 -800 102 4/26/2020 -900

I want to display from which last date the value change to negative for each ACCOUNT.

 ACCOUNT DATE AMOUNT 101 2/25/2020 -200 102 3/21/2020 -800
2 Solutions

Accepted Solutions

One solution is.

``````tab1:
LOAD *, If(K1>1 And Peek(AMOUNT)>0 And AMOUNT<0,'Y') As Flag;
ACCOUNT, DATE, AMOUNT
101, 1/2/2020, -500
101, 1/17/2020, 1000
101, 2/25/2020, -200
101, 2/29/2020, 300
102, 2/9/2020, 600
102, 2/11/2020, 700
102, 3/21/2020, -800
102, 4/26/2020, -900
];

Drop Field K1;``````
Specialist

Hi

try below

abc:
ACCOUNT, DATE, AMOUNT
101, 1/2/2020, -500
101, 1/17/2020, 1000
101, 2/25/2020, -200
101, 2/29/2020, 300
102, 2/9/2020, 600
102, 2/11/2020, 700
102, 3/21/2020, -800
102, 4/26/2020, -900
];

NoConcatenate
pqr:
DATE as Date,
AMOUNT as Amount
where AMOUNT<0;

ACCOUNT,
DATE,
if(Previous(AMOUNT)>0,AMOUNT)as AMOUNT
Resident abc;

6 Replies

One solution is.

``````tab1:
LOAD *, If(K1>1 And Peek(AMOUNT)>0 And AMOUNT<0,'Y') As Flag;
ACCOUNT, DATE, AMOUNT
101, 1/2/2020, -500
101, 1/17/2020, 1000
101, 2/25/2020, -200
101, 2/29/2020, 300
102, 2/9/2020, 600
102, 2/11/2020, 700
102, 3/21/2020, -800
102, 4/26/2020, -900
];

Drop Field K1;``````

Output.

Specialist

Hi

try below

abc:
ACCOUNT, DATE, AMOUNT
101, 1/2/2020, -500
101, 1/17/2020, 1000
101, 2/25/2020, -200
101, 2/29/2020, 300
102, 2/9/2020, 600
102, 2/11/2020, 700
102, 3/21/2020, -800
102, 4/26/2020, -900
];

NoConcatenate
pqr:
DATE as Date,
AMOUNT as Amount
where AMOUNT<0;

ACCOUNT,
DATE,
if(Previous(AMOUNT)>0,AMOUNT)as AMOUNT
Resident abc;

Partner - Creator II
Author

Thank You very much

Partner - Creator II
Author

It Works
thank you bor

I don't think solution using peek and previous will work here because let's say if you found negative value on 2/11/2020 for ID 102. For dynamic solution refer to below thread

https://community.qlik.com/t5/Qlik-Sense-App-Development/Display-Date-when-Value-become-negative/m-p...

Community Browser