Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
simonhoward
Partner - Contributor II
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:

    

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.

1 Solution

Accepted Solutions
sunny_talwar

Try this

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


Capture.PNG

View solution in original post

5 Replies
sunny_talwar

Try this

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


Capture.PNG

simonhoward
Partner - Contributor II
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:

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

Any thoughts on this?

sunny_talwar

May be this

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

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

Capture.PNG

simonhoward
Partner - Contributor II
Partner - Contributor II
Author

Thanks Sunny that works well.

urbanfaces
Contributor III
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