4 Replies Latest reply: Jul 3, 2017 6:02 PM by Ken Daniels RSS

    How do I show a total line in a line chart?

    Ken Daniels

      It seems this should be a trivial task, but I haven't found a way to do this in Qlik Sense.

       

      Given a two-dimensional line chart that shows revenue by office per month as shown in the screenshot below (and in attachment), is there a way to display not only one revenue line for each office but also a total (or average) revenue line for all displayed offices?

       

      x.png

       

      In the above example, I would like to see a 4th line that represents, for each month, the total (or the average) of the revenue for all 3 offices (Chicago, Dallas, Houston). Furthermore, if the user filters on offices to show only Chicago and Dallas, than I would want the total line not to include Houston revenue.

       

      I tried using the Reference Line add-on, but I was not able to make it show a total (or average) for each office, varying from month to month.

       

      Thanks in advance to anyone who can help me with this seemingly simple (but elusive in Sense) requirement.

        • Re: How do I show a total line in a line chart?
          Jahanzeb Hashmi

          in expression add 

          = colume1+ colume2 + colume3

            • Re: How do I show a total line in a line chart?
              Thomas Le Gall

              Hi Ken,

               

              Yes you can do this.

               

              Assuming you have a table of [Offices] as :

               

              Office_Code_Key / Office_Name / Office_Group

              1                           / Chicago         /  Blabla

              2                           / Dallas            /  Etc. Etc.

              3                           / Houston         / AnotherGroup

               

               

               

               

              You actual script to load this is :

               

              Offices:

              LOAD

              Office_Code_Key,

              Office_Name,

              Office_Group

              From Offices_Sources;




              You will have to change for this :


              Offices:

              LOAD

              Office_Code_Key,

              Office_Name,

              Office_Name as Line_Chart_Special_Office_Name,

              Office_Group

              From Offices_Sources;


              // if you want a line for total

              concatenate(Offices)

              LOAD

              Office_Code_Key,

              'Total' as Line_Chart_Special_Office_Name

              From Offices_Sources;


              // if you want a line for average

              concatenate(Offices)

              LOAD

              Office_Code_Key,

              'Average' as Line_Chart_Special_Office_Name

              From Offices_Sources;



              That way, every office will be linked to the 'Total' or 'average' Special_Office_Name




              Then, in your line chart :


              Dimensions:

              Months and Line_Chart_Special_Office_Name


              Measure :

              if(

              Line_Chart_Special_Office_Name='Average',

              sum(MeasureFieldYouUse)/count(distinct Office_Name), // only for average, it would be the total divided by offices

              sum(MeasureFieldYouUse) // both for normal lines and the total lines because ine the script every office will be linked to total

              )




              BR,

              Thomas Le Gall