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

[kwdaniels]

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?

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.

[zebhashmi]

in expression add 

= colume1+ colume2 + colume3

[thomaslg_wq · Accepted solution]

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]

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

[kwdaniels]

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.