Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
That is strange that it does that... but try this
Sum({<Status = {'B'}>}Aggr(FirstSortedValue({<Status = {'B'}>}Balance, Day), ID))
Maybe this?
if(Status='B'and Day=min({<Status={'B'}>}total <ID> Day) , Sum({<Status={'B'}>}Balance))
Try this
Sum(Aggr(FirstSortedValue({<Status = {'B'}>}Balance, Day), ID))
Tested it in QlikView, but should work in Qlik Sense also
Thank you, Sunny
but idont know why qsense shows like this:
That is strange that it does that... but try this
Sum({<Status = {'B'}>}Aggr(FirstSortedValue({<Status = {'B'}>}Balance, Day), ID))