Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kwdaniels
Partner - Creator
Partner - Creator

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

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.

1 Solution

Accepted Solutions
thomaslg_wq
Creator III
Creator III

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

View solution in original post

4 Replies
zebhashmi
Specialist
Specialist

in expression add 

= colume1+ colume2 + colume3

thomaslg_wq
Creator III
Creator III

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

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

I agree with Thomas

I use this method also for any other additional grouping in chart/table - I find it very easy to implement and efficient!

cheers

Lech

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
kwdaniels
Partner - Creator
Partner - Creator
Author

Thanks, Thomas. I had actually done some more searching and found a similar solution (Net Total line in Qlik Sense line chart), but I appreciate that you spelled it out so clearly for me and that you included the Average option.