Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ch_riadh
Partner
Partner

Display Date when Value become negative_

Hi, I have this table,

ACCOUNTDATEAMOUNT
1011/2/2020-500
1011/17/20201000
1012/25/2020-200
1012/29/2020300
1022/9/2020600
1022/11/2020700
1023/21/2020-800
1024/26/2020-900

 

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

ACCOUNTDATEAMOUNT
1012/25/2020-200
1023/21/2020-800
2 Replies
Kushal_Chawda

 

 

 

//Front end solution

Dimension:
ACCOUNT

Expressions:
1) DATE
=FirstSortedValue(DATE, - aggr(if(DATE< max(total <ACCOUNT> DATE) and AMOUNT<0,DATE),ACCOUNT,DATE))

2) AMOUNT
=FirstSortedValue(AMOUNT, - aggr(if(DATE< max(total <ACCOUNT> DATE) and AMOUNT<0,DATE),ACCOUNT,DATE))

//Backend solution

Data:
LOAD
ACCOUNT,
"DATE",
AMOUNT
FROM [lib://Web]
(html, utf8, embedded labels, table is @1);

Left Join(Data)
Load ACCOUNT,
     max(DATE) as MaxDate
Resident Data
Group by ACCOUNT;

Left Join(Data)
Load ACCOUNT,
      max(DATE) as DATE,
     1 as IsLatestNegative
Resident Data
where DATE<MaxDate and AMOUNT<0
Group by ACCOUNT;

Then create chart with below dimension & expression

Dimension:
1) ACCOUNT
2) DATE

Expression:
=sum({<IsLatestNegative={1}>}AMOUNT)

Annotation 2020-08-16 230538.png

Annotation 2020-08-16 231847.png

Kushal_Chawda

Updated solution