Pivot Table: Sum of Rows?

[#1]

Hello,

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

PRODUCT	ACCOUNT	COUNT
001	10	1
001	20	1
002	30	1
002	40	1
003	30	1
004	40	1
004	50	1
		5

But if I contract all, I see:

PRODUCT	COUNT
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!

[senpradip007]

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

[VishalWaghole]

Hi mart,

Please try with removing distinct word.

Like, Count(ACCOUNT)

Hope it work for you.

-- Regards,

Vishal Waghole

[sujeetsingh]

Pivot Table: Sum of Rows?

This is the same thread by you.

[SunilChauhan]

use

AGGR(count(distinct ACCOUNT),Account)

[SunilChauhan]

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

[muniyandi]

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]

Hi

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

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

HTH

Jonathan

[Anonymous]

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