Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

simonhoward
New 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:

    

StoreEmployeeSalarySale Amount
ABob50000
AAdam55000
ASteve45000
BGeoff47000
BSandra70000
BCharles56000
BFelicity51000
CBeth43000
CCarter42000
CDaniel39000
CSusan53000
CPamela57000
CErin46000
A 14963.95
A31631.62
A26528.53
A12708.55
A14206.99
A95192.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:

2018-04-04_16-19-25.png

2018-04-04_16-21-20.png

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.

Tags (2)
1 Solution

Accepted Solutions

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

Try this

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


Capture.PNG

4 Replies

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

Try this

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


Capture.PNG

simonhoward
New Contributor II

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:

2018-04-05_9-08-31.png

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))

Capture.PNG

simonhoward
New Contributor II

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

Thanks Sunny that works well.

Community Browser