4 Replies Latest reply: Apr 4, 2018 10:01 PM by Simon Howard

# Using 'Total <Dimension>' in a Pivot Table in Qlik Sense

Hi,

I am trying to use the total keyword in a pivot table, and am getting strange results. I am using a fact table structure similar to the following:

 Store Employee Salary Sale Amount A Bob 50000 A Adam 55000 A Steve 45000 B Geoff 47000 B Sandra 70000 B Charles 56000 B Felicity 51000 C Beth 43000 C Carter 42000 C Daniel 39000 C Susan 53000 C Pamela 57000 C Erin 46000 A 14963.95 A 31631.62 A 26528.53 A 12708.55 A 14206.99 A 95192.72 A 7123.50

.. and so on. The reason the Salaries are included in the fact table is that these values are attached to a particular payslip, and can change over time. The sales are not recorded against any particular employee, just against the store.

What I am trying to achieve is to display the sum of sales for that store, against each employee in a pivot table. The values are displayed correctly against the store, until a node is expanded, where each value is then turned to 0:

What I am trying to achieve is the Sum figure of \$437,227.12 is displayed in this column for Adam, Bob and Steve.

Thanks.

• ###### Re: Using 'Total <Dimension>' in a Pivot Table in Qlik Sense

Try this

Aggr(NODISTINCT Sum([Sale Amount]), Store)

• ###### Re: Using 'Total <Dimension>' in a Pivot Table in Qlik Sense

Thanks Sunny!

That worked well. The only caveat is that if I enable Totals for the Stores, we do not get a value there:

Any thoughts on this?

• ###### Re: Using 'Total <Dimension>' in a Pivot Table in Qlik Sense

May be this

If(Dimensionality() = 0, Sum(Aggr(Sum([Sale Amount]), Store)),

Aggr(NODISTINCT Sum([Sale Amount]), Store))

• ###### Re: Using 'Total <Dimension>' in a Pivot Table in Qlik Sense

Thanks Sunny that works well.