Solved: Re: How to add Aggregate data - Qlik Community

Anonymous · ‎2018-06-26

hi，

I want to use aggregate row to distinct data in straight，just like bellow：

But i found the straight table's aggregate can't do this,it can't use expression to distinct repeating data。Column1 is a dimension,used and subtotal is expression.So i want to add a aggregate row at the bottom by using expression to distinct repeating data.

Am i right ? and Who can help me？

Thks

ogautier62 · ‎2018-07-04

HI,

If I understand :

you want count for each line, and count distinct as subtotal ?

if so : in subtotal expression write :

if(dimensionality()=0,count(distinct user),count(user))

so subtotal 6, and not 7 because of a duplicate

regards

View solution in original post

sunny_talwar · ‎2018-06-26

What is the expressions for Used and subtotal here?

Anonymous · ‎2018-06-26

hi,

used:a data field from raw table,

subtatol:sum(used)

Anil_Babu_Samineni · ‎2018-06-26

I suggest you to use field in script like subfield(used) and used expression count(total <column1> distinct used)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

Anonymous · ‎2018-06-26

Anonymous · ‎2018-06-26

Now i need to get the total and distinct repeating data,not the subtotal.

ogautier62 · ‎2018-06-27

Hi,

use a sheet with column1 in dimension

in expression :

concat(used,',') to have 1,2,3,4

for total :

count(distinct used)

regards

ogautier62 · ‎2018-06-27

and for expression total

use option "total of the expression" and not sum of rows

Anonymous · ‎2018-06-27

hi,

Thks.

About:

for total :

count(distinct used)

is used for my subtotal ?

I'm new to qv, i have used a sheet with column1 in dimension, and have a expression by using concat(used,',') to have 1,2,3,4 , and use count expression to get avery row's subtotal number.

Now i want to get the aggergate total at the buttom. So, Where to be used this expression count(distinct used)?

ogautier62 · ‎2018-06-27

add an expression, with option total and it will display total count