Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
isciberras
Contributor

Group by and sum not working properly

Hi Everyone,

I'm having trouble using sum and group by in QLikview.


I have a table which looks like this:

UserIssue Type1Date
1101/01/2017
1001/01/2017
1102/01/2017
2102/01/2017
2102/01/2017
2103/01/2017
3003/01/2017
4104/01/2017
4104/01/2017
4004/01/2017
4105/01/2017

The aim is to have a result table like below:

User
sum Issue Type1
12
23
30
42

The code I am using is:

Pattern:

load

sum (Issue Type1) as [sum Issue Type1]

Resident Final

Where [Issue Type1] =1

Group by User;

___

However an example of  the result I am getting is:

Usersum Issue Type1
11
12
21
22
23

Can anyone help me out please? as I ran out of ideas. I have tried using count and or distinct however these did not work

Thanks,

Isaac

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Group by and sum not working properly

Shouldn't you add User as dimensional field in your Group by LOAD?

Pattern:

load

User,

sum (Issue Type1) as [sum Issue Type1]

Resident Final

Where [Issue Type1] =1

Group by User;

View solution in original post

4 Replies

Re: Group by and sum not working properly

Seems to be working

Table:

LOAD * INLINE [

    User, Issue Type1, Date

    1, 1, 01/01/2017

    1, 0, 01/01/2017

    1, 1, 02/01/2017

    2, 1, 02/01/2017

    2, 1, 02/01/2017

    2, 1, 03/01/2017

    3, 0, 03/01/2017

    4, 1, 04/01/2017

    4, 1, 04/01/2017

    4, 0, 04/01/2017

    4, 1, 05/01/2017

];


AggregatedTable:

LOAD User,

Sum([Issue Type1]) as [Sum Of Issue Type 1]

Resident Table

Group By User;


Capture.PNG

MVP
MVP

Re: Group by and sum not working properly

Shouldn't you add User as dimensional field in your Group by LOAD?

Pattern:

load

User,

sum (Issue Type1) as [sum Issue Type1]

Resident Final

Where [Issue Type1] =1

Group by User;

View solution in original post

isciberras
Contributor

Re: Group by and sum not working properly

Thank you sunny,

You are completely right it does work I'm so embarrassed my issue was that I wasn't linking  "User" between tables and this is why I had such an odd result. My appologies for taking up your time on this, thank you for all your help.

Isaac

isciberras
Contributor

Re: Group by and sum not working properly

Thank you Stefan,

Life saver too, not linking User was the problem - I feel so stupid. Thank you very much for taking you time to help me out you were a big help

Isaac