topic Calculate previous value with set analysis in App Development

Calculate previous value with set analysis

phongnnguyen — Tue, 14 Aug 2018 04:24:01 GMT

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

Re: Calculate previous value with set analysis

niclaz79 — Tue, 14 Aug 2018 07:16:59 GMT

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

Re: Calculate previous value with set analysis

phongnnguyen — Tue, 14 Aug 2018 07:45:17 GMT

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

Re: Calculate previous value with set analysis

raman_rastogi — Tue, 14 Aug 2018 07:50:48 GMT

Hi Phong

Try This

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

Best

Ramam Rastogi

Re: Calculate previous value with set analysis

tresB — Tue, 14 Aug 2018 08:34:28 GMT

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)

Re: Calculate previous value with set analysis

BalaBhaskar_Qlik — Tue, 14 Aug 2018 12:09:43 GMT

May be this:

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