4 Replies Latest reply: Dec 8, 2016 12:45 AM by Ozzie Boeuf RSS

    Help - Qlik Sense expressions containing strings and variables

    Ozzie Boeuf

      Hi,

       

      I'm attempting to represent the total using a combination of text, a dimension and a variable in a subtitle expressions as follows:

       

      'Total: ' & if(Title = 'Total Members', $(CurrPeriod),0)

       

      where Title is a dimension and CurrPeriod is a variable that is set using Let in the load scripts.

       

      I've spent some time using Google to find a suitable answer and tried many different suggestions but it is still not working.

       

      Any help getting this right will be appreciated.

       

      Thanks kindly,

      Ozzie

        • Re: Help - Qlik Sense expressions containing strings and variables
          Marcus Sommer

          Quite probably will Title return NULL because there is no aggregation on them and I think there will be more than one possible value for this dimension. Also for your variable I'm not sure if the expected result will be returned.

           

          How looked the statement for the variable and which result is expected? Further a screenshot with your object will be helpful.

           

          - Marcus

            • Re: Help - Qlik Sense expressions containing strings and variables
              Ozzie Boeuf

              Hi  Marcus,

               

              I appreciate your response to my post.  Below are some further details as requested.

               

              The subtitle line in the following chart shows the expression in question (Total: 8963 etc.)

              Gender Diversity.PNG

              In the above chart the Subtitle string is created using the following expression:

              'Total: ' & sum(if(Title = 'Total Members', [Nov],0)) & ' {' &

              'Female: ' & sum(if(Title = 'Females', [Nov],0)) & ',   Male: ' & sum(if(Title = 'Males', [Nov],0)) &'}'

               

              The above expression works fine but it is one of many such expressions in my executive overview app where the month dimension is “hard-coded”.  Every time the reporting period changes I have to find each of the 20 or so expressions and change them. Eg. [Oct] to [Nov] etc.  Tedious to maintain.

               

              I want to create a single point of maintenance in the load scripts using variables for re-use in the app whenever the reporting period changes rather than have to do many changes mentioned above.

               

              So after some research and reading in our Qlik forums, I created 2 Let statements at the bottom of the following load script:

              [Org Health Summary - P&C]:

              LOAD

              [DataSource],

              [Category],

              [Sub-Category],

              [Title],

              [Prev FY-1],

              [Prev FY],

              [Jul],

              [Aug],

              [Sep],

              [Oct],

              [Nov],

              [Dec],

              [Jan],

              [Feb],

              [Mar],

              [Apr],

              [May],

              [Jun]

              FROM [lib://Data/Our People.xlsx]

              (ooxml, embedded labels, header is 1 lines, table is [Org Health Summary - P&C]);

               

              Let CurrPeriod = 'sum(Nov)';

              Let PrevPeriod = 'sum(Oct)';

               

              From there, I tried testing the use of one of the variables by changing the first line of the Subtitle script so that it shows:

              'Total: ' & if(Title = 'Total Members', $(CurrPeriod),0)

               

              And this did not work. Title is not null and there is a value for the variable (8,963). After all, it did work using [Nov], which is what CurrPeriod is aggregating. But not working when using the variable as an aggregation.

               

              Look forward to hearing from you and others.

               

              Thanks,

              Ozzie