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: 
Anonymous
Not applicable

Need help with AGGR

Hello,

I have the below AGGR function in a button that selects all the visits wherever the KeyDate is null. However, I need to edit little bit so that the function can select all the visits where the KeyDate is null and Balance is not equal to zero.

=Aggr(if(isnull(KeyDate),VisitNumber),VisitNumber)

Could someone please help me modify to include the balance field.

Thanks much.

1 Solution

Accepted Solutions
sunny_talwar

My bad, forgot to add parenthesis. Try this:

='(' & Concat(DISTINCT {<VisitNumber = {"=Len(Trim(KeyDate)) = 0"}>} Chr(34) &VisitNumber & Chr(34), '|') & ')'

View solution in original post

10 Replies
sunny_talwar

May be this:

=Aggr(if(isnull(KeyDate) and Sum(Balance) <> 0, VisitNumber), VisitNumber)

Anonymous
Not applicable
Author

Hey Sunny,

Sorry, I am little confused. Clicking the button with AGGR is not selecting anything. How about a listbox expression to perform this? I am trying  =if(IsNull(KeyDate),'No KeyDate','VisitsWithKeyDate'). I need to select V0004 and V0005 in below example.

   

VisitNumberKeyDateBal
V00011/23/1985100
V00022/11/1985200
V00035/9/1986350
V0004 400
V0005 500
sunny_talwar

May be like this

Field

VisitNumber

Search String

='=Sum({<VisitNumber = {"=Len(Trim(KeyDate)) = 0"}>}Bal) > 0'

Anonymous
Not applicable
Author

My QlikView server is frozen. I am rebooting the server. I will try your expression in few mins.

Anonymous
Not applicable
Author

Wow Sunny! man ='=Sum({<VisitNumber = {"=Len(Trim(KeyDate)) = 0"}>}Bal) > 0' worked. Please do me a last favor. How about if we ignore the balance field now? How can I get all the visits that has null KeyDate regardless of balance.

sunny_talwar

May be try removing > 0 at the end and see if that works

Field

VisitNumber

Search String

='=Sum({<VisitNumber = {"=Len(Trim(KeyDate)) = 0"}>}Bal)'

sunny_talwar

or may be this:

Field

VisitNumber

Search String

Concat(DISTINCT {<VisitNumber = {"=Len(Trim(KeyDate)) = 0"}>} Chr(34) & VisitNumber & Chr(34), '|')

Anonymous
Not applicable
Author

The following did not work:

Concat(DISTINCT {<AccountNumber = {"=Len(Trim(KeyDate)) = 0"}>} Chr(34) & AccountNumber & Chr(34), '|')

sunny_talwar

My bad, forgot to add parenthesis. Try this:

='(' & Concat(DISTINCT {<VisitNumber = {"=Len(Trim(KeyDate)) = 0"}>} Chr(34) &VisitNumber & Chr(34), '|') & ')'