# New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

## Group by in qlikview and calculation on groupby

Hi All

What does group by does

Will it consider set of  rows as 1 row

I have a situation where

Employee id,  employee name, age, sur_name, country, orders

1                     bond                     26    james        usa         200 \$

3                     bond                      26   james        canada   500\$

2                      lisa                       22     ray            russia     20\$

2                       lisa                       22     ray            russia     40\$

My question is if   Employee id,  employee name, age, sur_name, country,   is same expect orders

consider it as one id  and the total orders is sum of both the rows ie 60 \$.

if  any value in  Employee id,  employee name, age, sur_name, country,   is different consider it as different record

the output in the above senario is

Employee id   total orders

1                     200\$

2                      60\$

3                      500\$

can we achieve it by any aggr function or group by in the script

1 Solution

Accepted Solutions
Contributor III

## Re: Group by in qlikview and calculation on groupby

Hi John,

Use simple straight chart and apply following

Dimension:- Employee id

Expression:- Sum(orders)& '\$'

you will get below table from that :

Employee idTotal Orders
1200\$
260\$
3500\$

Else you can use aggregate function also as following:

Dimension:- Employee id

Expression:- aggr(Sum(orders)& '\$',age,country,[employee name],sur_name)

you will get below table from that :

Employee idTotal Orders
1200\$
260\$
3500\$

Thanks,

16 Replies
Honored Contributor II

## Re: Group by in qlikview and calculation on groupby

try like this:

dimension:  Employee id

expression:  aggr(sum(orders),[employee name],sur_name,country,age)

hope this helps

New Contributor II

## Re: Group by in qlikview and calculation on groupby

Hi,

I believe if you create a simple report with Employee id and total Orders, you will receive it.

Yes, you can group by in script, please see the below.

Load Employee id, Employee name,age,sur_name,country , sum(total ordere)

From File

Group by Employee id, Employee name,age,sur_name,country;

Regards,

Nimesh Krishnan L

## Re: Group by in qlikview and calculation on groupby

Try like this

Dimension is employee id

Expression is

Sum(<[employee id]> total bond)

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)
Honored Contributor II

## Re: Group by in qlikview and calculation on groupby

You can do this in couple of ways as below:

Option I:

Dimension:  Employee_Id

Expression: Sum(Orders)

Option II:

If you want to use group by in the script then follow as below:

Source_Data:

Group by [Employee_id], [Employee name],age,sur_name,country;

Employee_id,  Employee name, age, sur_name, country, orders

1,                    bond,                    26,    james,        usa,        200

3,                    bond,                      26,  james,        canada,    500

2,                      lisa,                      22,    ray,            russia,    20

2,                      lisa,                      22,    ray,            russia,  40

];

Note: You can use group by only on the Resident table.

Partner

## Re: Group by in qlikview and calculation on groupby

Hi cena,

You can do it in script itself

Employee id,

employee name,

age,

sur_name,

country,

orders,

sum(orders) as TotalOrders

resident Table name

group by

Employee id, employee name,age,sur_name,country,orders;

Drop Table Table name;

Contributor III

## Re: Group by in qlikview and calculation on groupby

Hi John,

Use simple straight chart and apply following

Dimension:- Employee id

Expression:- Sum(orders)& '\$'

you will get below table from that :

Employee idTotal Orders
1200\$
260\$
3500\$

Else you can use aggregate function also as following:

Dimension:- Employee id

Expression:- aggr(Sum(orders)& '\$',age,country,[employee name],sur_name)

you will get below table from that :

Employee idTotal Orders
1200\$
260\$
3500\$

Thanks,

Not applicable

## Re: Group by in qlikview and calculation on groupby

Hi Frank,

what about the count, if i want to calculate the count of employee id based on the same senario, its not giving me correct results can you pls guide me

Honored Contributor II

## Re: Group by in qlikview and calculation on groupby

what would be the expected output?

Not applicable

## Re: Group by in qlikview and calculation on groupby

can u place the qvw please