Aggr in set analysis

[theaokii]

Hi,

I have a dataset include: the balance and status of customer by date

Im tending to sum the customers' balance on the first day that their status changed into 'B'

Data:
LOAD *,
	 Month(Day) as Month,
	 Date(MonthStart(Day)) as MonthYear;
LOAD Date(Date#([Day], 'DD-MM-YYYY') ) AS [Day],
	[ID],
	[Balance],
	[Status] INLINE [
    Day, ID, Balance, Status 
    15-08-2018, 001, 100000, A 
    16-08-2018, 001, 100000, A 
    17-08-2018, 001, 120000, B 
    18-08-2018, 001, 120000, B 
    16-08-2018, 002, 50000, B 
    17-08-2018, 002, 50000, B 
    18-08-2018, 002, 50000, C
    15-08-2018, 003, 100000, A 
    16-08-2018, 003, 100000, A 
    17-08-2018, 003, 120000, B 
    18-08-2018, 003, 120000, C
    17-08-2018, 004, 50000, B 
    18-08-2018, 004, 50000, C 
];

Result is:

Day	Balance
16-08-2018	50000
17-08-2018	290000

I just found out the first day that status changed: aggr(min({<status={'B'}>} day),ID)

but cant use it to add the above aggr into the sum balance

Anyone know how to do it?

Thanks in advance.

[OmarBenSalem]

Maybe this?

if(Status='B'and Day=min({<Status={'B'}>}total <ID> Day) , Sum({<Status={'B'}>}Balance))

[sunny_talwar]

Try this

Sum(Aggr(FirstSortedValue({<Status = {'B'}>}Balance, Day), ID)) 

Tested it in QlikView, but should work in Qlik Sense also

[theaokii]

Thank you, Sunny

but idont know why qsense shows like this:

[theaokii]

Thank you but i want the result by the day like:
Day Balance
16-08-2018 50000
17-08-2018 290000

[sunny_talwar · Accepted solution]

That is strange that it does that... but try this

Sum({<Status = {'B'}>}Aggr(FirstSortedValue({<Status = {'B'}>}Balance, Day), ID))

[theaokii]

Thank you alot. you saved me once again.
It works perfectly.