Announcements
cancel
Showing results for
Did you mean:
Partner - Contributor II

## 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.

1 Solution

Accepted Solutions
MVP

Try this

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

5 Replies
MVP

Try this

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

Partner - Contributor II
Author

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?

MVP

May be this

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

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

Partner - Contributor II
Author

Thanks Sunny that works well.

Contributor III

Hello Sunny and Simonhoward . I am trying to accomplish the same thing.  Sunny you said "The only caveat is that if I enable Totals for the Stores, we do not get a value there"?  So how were you able to have the formula to work? Did you replace Stores with another field? Thanks