3 Replies Latest reply: Jun 15, 2015 5:56 PM by Steve Lord RSS

    Expressions with fields in alternate states help

    Steve Lord

      Hi Community,

       

      I am trying to setup something where people can select a date range for one activity A in one box, a date range for activity B in another box, and a list of tertiary activities (C) in a third box.  Then have final counts pop up all at once.

      Counts would be like:

      How many people completed A

      How many people completed B

      How many people completed A and B

      How many people completed A,B, and X items from C.

       

      I made a text box with this expression to count the number of people with a questionnaire completed (activity A) irrespective of selections made in other list boxes on the tab::

      =count(Distinct {<[Biometrics Source],[TestMonthYear],[TestDate Floored],[Incentive_Season],[ShortName]>} if(len(QuestionnaireCompletedDate)>0, UserId))

       

      I have similar expressions for a couple of other counts, but then I realized I needed to put the list boxes themselves into alternate states to keep them from constraining each other, and thus indirectly constraining the counts in the text boxes.

       

      The list boxes are in three alternate states now:

      AR Questionnaire (Questionnaire_ listboxes)

      AR Biometric (Test_ listboxes)

      AR Incentive (Incentive/Shortname_ listboxes)

       

      What is the syntax for me to incorporate these alternate states back into the first expression above?  I want it to be affected by the AR Questionnaire state, and to ignore the Biometric and Incentive state selections when giving it's count of the Questionnaires completed.

       

      Thanks!

       

      (Master Calendar not suitable, because activities are so independent.  We often want to know how many people who completed a Questionnaire December 2014 also completed a Screening January - December 2015 or like that.)

        • Re: Expressions with fields in alternate states help
          Steve Lord

          Actually, I think this might be it:

           

          =count(Distinct {<[Biometrics Source],[TestMonthYear],[TestDate Floored],[Incentive_Season],[ShortName]>} {[AR HA]} if(len(QuestionnaireCompletedDateFloored)>0, UserId))

           

          It put the alternate state designation of the questinnairecompleteddate listbox back in front of the if(len(questionnairecompleteddate) bit and selections in the alternate state listbox are toggling the count again now.

           

          And I can probably chuck the stuff in the <> since I put those list boxes into different alternate states already.  So maybe I'm back to:

          =count(Distinct {[AR HA]} if(len(QuestionnaireCompletedDateFloored)>0, UserId))

           

          (I read through a pile of comments in this other post and bumped into one followup comment I was able to relate to my situation: Alternate states syntax)

           

          Any yes/no to validate my thoughts is welcome!

            • Re: Expressions with fields in alternate states help
              Steve Lord

              My actual solution is count(Distinct if(len({[State1]::QuestionnaireCompletedDate})>0, UserId))

              Then count(Distinct if(len({[State2]::TestDate})>0 AND {[State2]::Source}='Verified', UserId))

              so on and so forth.  This will let me put the listboxes into different states so they don't constrain each other's possible selections, then direct the other table/object expressions to the relevant selections in those listboxes.

               

              15.6 in the reference manual was helpful in telling me my previous syntax thought was bad, then I wandered on to 68.1 which had a simple and less simple expression that were more relevant.

              (Sorry to give myself points marking my own answer correct, but I want this visible as an answered question and I'm a zillion points from the next tier anyway, so should be minimal harm.   Key requirement for me was to make it so a user can make all needed selections in one pass, then transcribe or export all needed answers to a dozen different questions without further ado.)

               

              Relevant excerpts from the 2013 Qlikview 11 reference manual.  Chapters 15.6 and 68.1.

              ------------------------------

              15.6 Alternate States and Bookmarks

              Bookmarks capture selections in all states defined in the QlikView document. When recalling a bookmark

              created in version 11 (or later) the selections in all states will be applied.

              It is possible to make use of specific states contained in a bookmark within an expression. For example, this

              expression calculates Sales over the set generated by the selections defined in bookmark BM01 for the state

              ’Group 1’.

              Example:

              sum({[Group 1]::BM01} Sales)

               

              68.1

              An expression can be based on an alternate state.

              Examples:

              sum({[Group 1]} Sales)

              calculates sales based on the selections in the state ‘Group 1’.

              sum({$} Sales)

              calculates sales based on the selections in the default state.

              Both of these expressions can exist in a single chart. This allows end-users to compare multiple states

              within a single object. State references within expressions override the state of the object.

              Selections in a field in one state can be used as modifiers in another state.

              Example:

              sum({[Group 1]<Region = $::Region>} Sales)

               

              This syntax uses the selections in the Region field from the default state and modifies the state

              ‘Group 1’ with them. The effect is to keep the Region field "synchronized" between the default state

              and ‘Group 1’ for this expression.

            • Re: Expressions with fields in alternate states help
              Steve Lord

              Thought I had the right syntax, but it turned out to be this other syntax with asterisks:

              https://community.qlik.com/thread/87834

              count(Distinct {[AR HA]*[AR Biometric]*[AR Incentive]} UserId)

              My expression is responsive to selections in all three of those states now.