Building an Average Row or Column in a QlikView Pivot Table

By:

Simply using the RowNo() function nested with an IF() statement will give you full control of the Total row or column in a Pivot Table to use as you wish.

Pivot tables. Easily the most versatile of all the out of the box charts QlikView has to offer you. In fact, they are so versatile that their overuse is pandemic. From a visual analytics perspective, they’re not very, well, visual. It’s hard to spot trends or pick out outliers but no matter how hard you try to avoid them, most of your projects will include a pivot table somewhere in the document.

Excel Anarchy

Once I’ve gotten over my Excel and Pivot Table snobbery and embraced their corporate use, they soon become ubiquitous. It’s not long before you start getting requests like this:

Hello Mr/Mrs Vis Analytics Developer. Please put this Excel Document into QlikView. Keep the same layout and format…

Spend 5 minutes explaining why this would be a great opportunity to introduce some visual sexiness into the asker’s process and how you could tell the exact same story much faster with a simple line chart, or a heatmap or whatever, just not a pivot table. Finally return to your desk and figure out how to unpick the anarchy that went into that Excel document and work it into QlikView.

Okay, so now we’ve accepted that Pivot Tables and Excel are a fact of corporate life, lets figure out how to get the most out of them. Sooner or later, someone will ask you to add an average row or column to the pivot. This is no problem if you’re using a Straight Table, but unfortunately, there is no option for an Average column in a pivot table as the two following diagrams demonstrate:

Chart properties dialogue for a straight table
Chart properties dialogue for a straight table. Note the various options in Total Mode.

Chart properties dialogue for a pivot table
Chart properties dialogue for a Pivot Table. Note that total mode is disabled.

So unfortunately, this rules out anything other than a total for a Pivot Table. Lets see what the Qlik community has to say:

avg(aggr(current column expression,dimension1,dimension2,...))
// hat tip to John Witherspoon for this
// https://community.qlik.com/people/JohnW

Most of the workarounds on the community point to the use of the aggr() function. There are worse things you could do than use aggr() but it brings with itself a processing overhead and you don’t get the built in formatting you get with the total mode. If you can live with that, then read no further, however, if you want to see a different way of doing it, then read on…

Tricking the Pivot Table

It feels good to get one over on the Pivot Table for once. If you throw RowNo() into a new expression in your Pivot Table, you will get something like this:

RowNo() in the last column. Note that RowNo() returns 0 in the Total row.
RowNo() in the last column. Note that RowNo() returns 0 in the Total row.

Ok, so RowNo() returns 0 in the Total column. Lets see if we can hijack that with a simple If():

If(RowNo()=0,'Yes!!!','Nope')
RowNo() gives us the ability to manipulate what happens in each cell.
RowNo() gives us the ability to manipulate what happens in each cell.

Well, look at that! It works. Okay, lets see if we can use that in a more useful way. Let’s edit the first expression, to include an If() clause and an avg() function to calculate the the average of all the orders for the year:

If(RowNo()=0
 ,RangeAvg(Top(count(OrderID),1,(NoOfRows())))
 ,Count(OrderID)
)
A pivot table showing an average instead of a total
The completed table showing an average in a Pivot Table.

Amazing, it works! If you’re wondering why I used the RangeAvg() function rather than just a straight avg() well, that’s because RangeAvg() is an inter-record function which means that, rather than recalculating the averages, it is, in effect, reading the pre-processed averages from the already computed cells. Not much benefit in this case, but a good habit to get into. You’ll also notice that I’ve changed the label from Total to Average. That was just a case of changing the Label For Totals field on the Presentation Settings Tab:

Pivot Table - Presentation Settings
The Pivot Table showing the Presentation Tab and how we have changed the Total label to Average (highlighted)

.

Final Word, it works for columns too

So, this example shows your averages in the last row of the table, but you can also have the computation done in a column too, ie, an average of your rows. I’ll leave you to figure out how you would do that, with one little bit of help. You’ll probably want to make use of the ColumnNo() and First() functions in place of RownNo() and Top().


4 Replies to “Building an Average Row or Column in a QlikView Pivot Table”

  1. Hi, and what about this version?

    avg(aggr(count(OrderID),Month))

    it will keep the value in each row (cause the aggregation value will be equals to the row value), but in the total row will evaluate the avg() taking all values.

    Best regards!

  2. Hi Héctor,
    Thank you for taking the time to comment. Yes indeed, that is a valid solution and has its use cases. I discuss an identical approach (ref John Witherspoon’s original solution) in this post. The technique to identify the total column and execute a conditional expression means that we can use a range function (rangeavg()), which in turn utilises the inter-record arrays meaning far less computation than an aggr() statement.
    George

  3. Hi George,

    this is an excellent solution to show an average. Can you think of a way to adjust this to show a second (third, fourth etc) row of ‘totals’ if you wanted to show min and max for each column?

    1. Hi John, sorry for the late reply. I can’t think of a way where you could additional rows of data like this at the same time, however, it would be possible to put the entire formula into a variable, then control the value of the variable using a button or list item. That way you can control the values shown in the row. You can even change the value of the label (to change it from Average to Max etc).
      Hope that helps.

Leave a Reply

Your e-mail address will not be published. Required fields are marked *