Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
theaokii
Contributor III
Contributor III

Aggr in set analysis

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:

 

DayBalance
16-08-201850000
17-08-2018290000

 

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.

1 Solution

Accepted Solutions
sunny_talwar

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

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

image.png

View solution in original post

6 Replies
OmarBenSalem

Maybe this?

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

Capture.PNG

sunny_talwar

Try this

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

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

image.png

 

theaokii
Contributor III
Contributor III
Author

Thank you, Sunny

but idont know why qsense shows like this:

Sunny1.png

theaokii
Contributor III
Contributor III
Author

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

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

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

image.png

theaokii
Contributor III
Contributor III
Author

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