Calculate previous value with set analysis

[phongnnguyen]

Hi guys,

I have a problem with  my set analysis. My data like this:

AEID	Clientid	Tradedate	Type	Amount
AE1	001z0001	01/01/2017	Reactive	1000
AE1	001z0001	31/12/2017	Old	2000
AE1	001z0002	01/01/2017	Old	3000
AE1	001z0003	01/01/2017	Old	4000
AE1	001z0002	31/12/2017	Old	5000
AE1	001z0003	31/12/2017	Old	6000
AE1	001z0006	10/09/2017	New	200
AE1	001z0008	03/02/2018	New	100
AE1	001Z0009	06/05/2017	Reactive	4000

I want to have result total Amount of AE1 in 2017 with LAST Type is "Old".  It's mean AE1 = 21000 ( in this case ClientID : 001Z0001 has 2 types: Reactive and Old, but take LAST type( newest ) is OLD)

I use this set analysis but it isn't my expectation

sum(

{<TradeDate={">=$(=date(yearstart (max(TradeDate), -1),'YYYY-MM-DD'))<=$(=date(yearend(max(TradeDate),-1),'YYYY-MM-DD'))"}

    ,Type={'Old'}

    >} Amount)

Please give me the solution with this trouble. Thanks for your help

[niclaz79]

Sum({<Type -={'New'}>}if(Year(TradeDate) = 2017,Amount))

[phongnnguyen]

Hi Niclas,

I understand your set analysis, but it is not my expectation. I have edit the data, Actually, Just sum OLD LAST status. your code will be sum all row without New

[raman_rastogi]

Hi Phong

Try This

Sum({< Type={'Old'},Tradedate ={">=$(=YearStart(max(Tradedate_N),-1)) <=$(=YearEnd(max(Tradedate_N),-1))"} >}Amount)

Best

Ramam Rastogi

[tresesco]

What is your expected output from the sample data above? Like this?

If so, try like:

FirstSortedValue( {< Type={'Old'},Tradedate ={">=$(=YearStart(max(Tradedate_N),-1)) <=$(=YearEnd(max(Tradedate_N),-1))"}>} Aggr(Sum(Amount),AEID,Clientid,Tradedate), - Tradedate)

[balabhaskarqlik]

May be this:

Sum({<Type={'Old'},Tradedate ={">=$(=YearStart(max(Tradedate),-1)) <=$(=YearEnd(max(Tradedate),-1))"}>} Aggr(Sum(Amount),Clientid,Tradedate))