topic Re: Using 'Total <Dimension>' in a Pivot Table in Qlik Sense in App Development

Using 'Total ' in a Pivot Table in Qlik Sense

simonhoward — Wed, 04 Apr 2018 06:28:27 GMT

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 ' in a Pivot Table in Qlik Sense

sunny_talwar — Wed, 04 Apr 2018 15:12:03 GMT

Try this

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

Re: Using 'Total ' in a Pivot Table in Qlik Sense

simonhoward — Wed, 04 Apr 2018 23:11:37 GMT

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 ' in a Pivot Table in Qlik Sense

sunny_talwar — Thu, 05 Apr 2018 00:29:51 GMT

May be this

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

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

Re: Using 'Total ' in a Pivot Table in Qlik Sense

simonhoward — Thu, 05 Apr 2018 02:01:26 GMT

Thanks Sunny that works well.

Re: Using 'Total ' in a Pivot Table in Qlik Sense

urbanfaces — Thu, 25 May 2023 19:53:51 GMT

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