# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for
Did you mean:
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
Creator III

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
Master II

try like this:

dimension:  Employee id

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

hope this helps

Contributor II

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

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)
Master II

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

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;

Creator III

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
Author

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

Master II

what would be the expected output?

Not applicable
Author

can u place the qvw please