Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
My bad, forgot to add parenthesis. Try this:
='(' & Concat(DISTINCT {<VisitNumber = {"=Len(Trim(KeyDate)) = 0"}>} Chr(34) &VisitNumber & Chr(34), '|') & ')'
May be this:
=Aggr(if(isnull(KeyDate) and Sum(Balance) <> 0, VisitNumber), VisitNumber)
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.
VisitNumber | KeyDate | Bal |
V0001 | 1/23/1985 | 100 |
V0002 | 2/11/1985 | 200 |
V0003 | 5/9/1986 | 350 |
V0004 | 400 | |
V0005 | 500 |
May be like this
Field
VisitNumber
Search String
='=Sum({<VisitNumber = {"=Len(Trim(KeyDate)) = 0"}>}Bal) > 0'
My QlikView server is frozen. I am rebooting the server. I will try your expression in few mins.
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.
May be try removing > 0 at the end and see if that works
Field
VisitNumber
Search String
='=Sum({<VisitNumber = {"=Len(Trim(KeyDate)) = 0"}>}Bal)'
or may be this:
Field
VisitNumber
Search String
Concat(DISTINCT {<VisitNumber = {"=Len(Trim(KeyDate)) = 0"}>} Chr(34) & VisitNumber & Chr(34), '|')
The following did not work:
Concat(DISTINCT {<AccountNumber = {"=Len(Trim(KeyDate)) = 0"}>} Chr(34) & AccountNumber & Chr(34), '|')
My bad, forgot to add parenthesis. Try this:
='(' & Concat(DISTINCT {<VisitNumber = {"=Len(Trim(KeyDate)) = 0"}>} Chr(34) &VisitNumber & Chr(34), '|') & ')'