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

[simonhoward]

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.

[sunny_talwar · Accepted solution]

Try this

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

[simonhoward]

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?

[sunny_talwar]

May be this

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

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

[simonhoward]

Thanks Sunny that works well.

[urbanfaces]

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