2 Replies Latest reply: Feb 21, 2015 8:40 AM by bruce lee RSS

    Create Listbox for Yes/No filters to count null values for a field

      Hi all,


      I'd like to create a listbox with Yes/No for complete/incomplete (null values) on a field which I've thrown into a table.


      For eg: The table in the following screenshot shows Dates in the "Completed" field can be a date or "None" for null:




      This is because I have the following set up in my script:



      Set NullValue = 'None';


      I'm currently successfully counting what I need with the expression:


      =Count({<CompletedActivity = {'None'}>} CompletedActivity)


      However as you can see this number only displays in the title (I guess a little confusing for me here too).


      What I want (or perhaps any suggestions you may have for optimal functionality) is a listbox with Yes/No which filters by CompletedActivity='None' and CompletedActivity <> 'None'.


      I've already tried this in the script with:



      //Load * inline



      //=Count({<CompletedActivity <> {'None'}>} CompletedActivity),Yes

      //=Count({<CompletedActivity = {'None'}>} CompletedActivity),No



      which simply craps out the raw strings in the table rather than computes a count (as per http://i.imgur.com/lZ4Geim.png).


      I'm new to qlik but am currently stuck so I would greatly appreciate your suggestions by way of a demonstration for my learning purposes - thank you so much!

        • Re: Create Listbox for Yes/No filters to count null values for a field
          Chris Deniziak

          You can create a table by doing a resident load on your previously loaded table in the script. It would look similar to the attached. You can do things a bit differently based on how you want to group, but the overall idea is there.


          Let me know if you need further explanation.

            • Re: Create Listbox for Yes/No filters to count null values for a field

              Thank you very much good sir!!


              Your solution will be marked as correct but just as a caveat to others: My 'completedactivity' was a date and I could see the example values that Chris put in the load were example numbers - which clearly would work when CompletedActivity = 'None' but not when it wasn't :-)


              For Chris: I dunno if this was a long-winded solution but I simply created another date field and ran the month() function on it (first function that popped into my head), then I populated the LOAD statement as:



              LOAD * INLINE [

















              Which took into account all possible date values to filter (as there are only 12 months )


              This is most likely not the cleanest approach to a solution but it works - if any others have any suggestions I am as ever open and grateful!