Group by Total Values

[Demlet]

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!

[Lisa_P]

Are you using QlikView or Qlik Sense ?

[ogautier62]

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 · Accepted solution]

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]

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