Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
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), '|') & ')'