Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
Contributor II

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
Highlighted

Re: Need help with AGGR

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
Highlighted

Re: Need help with AGGR

May be this:

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

Highlighted
Contributor II

Re: Need help with AGGR

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
Highlighted

Re: Need help with AGGR

May be like this

Field

VisitNumber

Search String

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

Highlighted
Contributor II

Re: Need help with AGGR

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

Highlighted
Contributor II

Re: Need help with AGGR

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.

Highlighted

Re: Need help with AGGR

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

Field

VisitNumber

Search String

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

Highlighted

Re: Need help with AGGR

or may be this:

Field

VisitNumber

Search String

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

Highlighted
Contributor II

Re: Need help with AGGR

The following did not work:

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

Highlighted

Re: Need help with AGGR

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