Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

capriconuser
New Contributor III

group by sum

i have this kind of data

abc     a         single               213

abc     b          single               123

abc    c          single                  123

abc    d          single                  1

abc    a         multiple               123

abc    b          multiple               123

abc    c          multiple               123

abc    d          multiple               234

abc1     a         single               213

abc1     b          single               123

abc1    c          single                  123

abc1    d          single                  1

abc1    a         multiple               123

abc1    b          multiple               123

abc1    c          multiple               123

abc1    d          multiple               234

i want to get sum like this

abc     a         single               213

abc     b          single               123

abc    c          single                  123

abc    d          single                  1

sum                                         3465(whatever the sum is i just wrote random sum)

abc    a         multiple               123

abc    b          multiple               123

abc    c          multiple               123

abc    d          multiple               234

sum                                         3465(whatever the sum is i just wrote random sum)

abc1     a         single               213

abc1     b          single               123

abc1    c          single                  123

abc1    d          single                  1

sum                                         3465(whatever the sum is i just wrote random sum)

abc1    a         multiple               123

abc1    b          multiple               123

abc1    c          multiple               123

abc1    d          multiple               234

sum                                         3465(whatever the sum is i just wrote random sum)

how i get this

Tags (3)
6 Replies
deepali_more
New Contributor III

Re: group by sum

go for Pivot table

capriconuser
New Contributor III

Re: group by sum

ok but in pivot i get data like this

col1

col2

col3

col4

sum(col4)

abc

a

mutliple

123

123

single              

213

213

b

mutliple

123

123

single              

123

123

c

mutliple

123

123

single              

123

123

d

mutliple

234

234

single              

1

1

abc1

a

mutliple

123

123

single              

213

213

b

mutliple

123

123

single              

123

123

c

mutliple

123

123

single              

123

123

d

mutliple

234

234

single              

1

1

but actually i want get data like this

single

abc

a

b

c

d

213

123

123

1

Sum                     457

      multiple

abc

a

b

c

d

123

123

123

234

Sum                   603

single

abc1

a

213

b

213

c

123

d

1

        sum 547

        multiple

abc1

a

123

b

123

c

123

d

234

        sum603

is this possible how to group by col4 as headers

sasiparupudi1
Honored Contributor III

Re: group by sum

May be like attached

deepali_more
New Contributor III

Re: group by sum

Cross tables ..not sure though

rajasaravananm
New Contributor II

Re: group by sum

Use pivot table add col1 and col 2 into Row and put sum(col4) in column area.you will get the same result.

mitchjb2008
New Contributor III

Re: group by sum

The solution is to use TOTAL in your Sum expression. See attached application...

The expression should look like this:

Sum(TOTAL <col1,col3> col4)

This takes the sum of col4, but groups by col1 and col3. This will avoid showing the sum in terms of col2. Notice that the Totals at the top of the column is actually correct, not the sum of all the numbers below it!

Also, please note the sort order; I changed it to make the data look like your desired order.

Capture.JPG

Community Browser