Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

baochang
New Contributor II

How to add Aggregate data

hi,

I want to use aggregate row to distinct data in straight,just like bellow:

1530016818(1).jpg

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

1 Solution

Accepted Solutions
ogautier62
Valued Contributor II

Re: How to add Aggregate data

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

23 Replies

Re: How to add Aggregate data

What is the expressions for Used and subtotal here?

baochang
New Contributor II

Re: How to add Aggregate data

hi,

used:a data field from raw table,

subtatol:sum(used)

Re: How to add Aggregate data

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
baochang
New Contributor II

Re: How to add Aggregate data

1530068172(1).jpg

baochang
New Contributor II

Re: How to add Aggregate data

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

ogautier62
Valued Contributor II

Re: How to add Aggregate data

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
Valued Contributor II

Re: How to add Aggregate data

and for expression total

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

baochang
New Contributor II

Re: How to add Aggregate data

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
Valued Contributor II

Re: How to add Aggregate data

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