topic Re: Display Date when Value become negative_ in App Development

Display Date when Value become negative_

ch_riadh — Sun, 16 Aug 2020 08:58:36 GMT

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

Re: Display Date when Value become negative_

Kushal_Chawda — Sun, 16 Aug 2020 22:20:19 GMT

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

Re: Display Date when Value become negative_

Kushal_Chawda — Sun, 16 Aug 2020 21:42:10 GMT

Updated solution