Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Demlet
Contributor III
Contributor III

Group by Total Values

Hello Qlik Community,

I have seen various ways to group data using aggr and I can't seem to get this to work.

In a chart I have a dimension (UserID). I have a count of (Submissions). I can see how many Submissions each UserID has submitted. I want to group by these totals. For example I want to see how many Users submitted 1, or submitted 2, or submitted 47, etc...

The ideal result would be seeing this in a histogram or other chart where I could see the Total Amount of Users per Submission totals.

Example:

1,000 users had 1 submissions, 500 users has 2 submissions, etc.

Any ideas? I am open to Data load editor, or set analysis - whichever would be best practice in your mind.

Here is an example of what I was trying in set analysis, but couldn't get it to show up right in a table or chart. Please note I had some filters for the Submission type.

=Aggr(Count({$<[SOURCE]={'sourcevalue'},[TYPE]={'typevalue'}>} DISTINCT SUBMISSIONID),USERID)

-Thanks!

1 Solution

Accepted Solutions
DavidŠtorek
Creator III
Creator III

Hi,

I would suggest you to create a table which will consist of only two columns. UserID and number of submissions. Something like this.

SubmissionsCount:

Load

UserID

count(SUBMISSIONID) as numbeOfSubmissions

resident <your primary table>

group by UserId;


and than just simply use histogram chart where you will use field numberOfSubmission.

That's it . Hope this will help you.

View solution in original post

4 Replies
Lisa_P
Employee
Employee

Are you using QlikView or Qlik Sense ?

ogautier62
Specialist II
Specialist II

Hi,

in dimension :

aggr(count distinct submissionID,userID)

in expression :

count(distinct userId)

and you order by the expression

but may be it's not axactly what you want ?

regards

DavidŠtorek
Creator III
Creator III

Hi,

I would suggest you to create a table which will consist of only two columns. UserID and number of submissions. Something like this.

SubmissionsCount:

Load

UserID

count(SUBMISSIONID) as numbeOfSubmissions

resident <your primary table>

group by UserId;


and than just simply use histogram chart where you will use field numberOfSubmission.

That's it . Hope this will help you.

Demlet
Contributor III
Contributor III
Author

Thanks David,

This was a very clean solution using the Data Load Editor. Much easier then trying the same thing in Set Analysis. Your response is much appreciated.

Best regards,

-Derek