Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pivot Table: Sum of Rows?

Hello,

I have a Pivot Table where there is something like this:

PRODUCTACCOUNTCOUNT
001101
001201
002301
002401
003301
004401
004501
5

But if I contract all, I see:

PRODUCTCOUNT
001       +2
002       +2
003       +1
004      +2
5

Where:

- Product: Dimension

- Account: Dimension

- Count: Expression

The Expression Count is this:

COUNT (DISTINCT ACCOUNT)

So it counts the distinct accounts for every product

That's why it shows me 5 instead of 7...

I'd like to show the value as a Sum of Rows (like in Simple Table, but I need to use this Pivot Table), in order to show me 7.

Do you know how could I do this?

Thank you!

9 Replies
senpradip007
Specialist III
Specialist III

could you please upload qvw?

PrashantSangle

Hi,

When you are using DISTINCT , It will give you count of only Distinct Account which is in your case

10,20,30,40,50

Therefore it is showing you Count =5,

If you want to Count of All account even if they are repeated then try with

Simple count(ACCOUNT) instead of trying with count(distinct ACCOUNT).

Or you can try with Aggr() also,

Like,

AGGR(count(distinct ACCOUNT),PRODUCT)

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
VishalWaghole
Specialist II
Specialist II

Hi mart,

Please try with removing distinct word.

Like, Count(ACCOUNT)

Hope it work for you.

-- Regards,

Vishal Waghole

sujeetsingh
Master III
Master III

Pivot Table: Sum of Rows?

This is the same thread by you.

IamBack
Champion
Champion

use

AGGR(count(distinct ACCOUNT),Account)

IamBack
Champion
Champion

or you can use straight table instead and check sum (no of rows) after selecting expression on expression tab

muniyandi
Creator III
Creator III

Hi Mart,

In your chart have Account are dimension , then you will use count(Account) expression fine. for your requirement.

Kindly find the attachment file.

Thanks,

Muni

jonathandienst
Partner
Partner

Hi

If you need 'sum of rows' in a pivot table,use this for your expression: 

Sum(Aggr(COUNT (DISTINCT ACCOUNT), PRODUCT))

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
s33pie007
Creator
Creator

Hi Jonathan, I found your solution very helpful, thanks for that btw.

I do have a different issue now. I wish to implement the aggr function into a Qlik Sense application.

this makes things very difficult for me as the users decide on which dimensions they wish to see their totals.

Is there a way to make the aggr expression dynamic? lets say the users drags in PRODUCT and ACCOUNT,

the aggr function must then cater for both the dimensions.

It's a bit rocket sciency for me.

help will be appreciated.

thanks,

Stefan