Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Count of Records Related

Good Afternoon, 

 

I have a requirement on a table that I am having difficulty in fulfilling. I need a column that lists a count for the total number of users related to the same event. For instance, I have a single event record from the database that 5 people are associated with that event. I have a temp table that is calculating a count of the individuals per event. Where I am having trouble though is the customer wants to see 1 of 5, 2 of 5, 3 of 5, 4 of 5, and 5 of 5 in the table. Currently for each person associated with that event it displays the total 5, as that is the total for the event. I am trying to figure out if it is possible to do as described above? Any suggestions would greatly be appreciated. 

 

Thanks,

Phil Radovich

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Champion III
Champion III

Another solution in UI chart expr try like

Using straight table add dims as
PersonKey, EventKey
3-Exprs
=Aggr(Rank(-PersonKey,4,1), EventKey, PersonKey) //Label this as Ranking
=Count(TOTAL <EventKey> EventKey) //Label this as Cnt
=Ranking & ' of '& Cnt //This will give you count of individuals

Capture.PNG

View solution in original post

6 Replies
Highlighted
Champion III
Champion III

Can you share some sample data with expected output please? Will help to help you faster.
Highlighted
Contributor III
Contributor III

I am hoping for something like the attached screenshot. 

 

Sampleset.PNG

 

Highlighted
Champion III
Champion III

This is the solution which is script based. Let me know if this helps

T1:
LOAD *, AutoNumber(PersonKey, EventKey) AS Cnt Inline [
PersonKey, EventKey
1,1
2,1
3,1
4,1
5,1
6,2
7,2
8,2
9,2
10,3
11,3
12,3
];

LEFT JOIN(T1)
LOAD EventKey,
Count(PersonKey) AS RowCnt
Resident T1
Group By EventKey;


NoConcatenate
LOAD *, Cnt&' Of '& RowCnt as CntIndividuals
Resident T1;

Drop Table T1;
Highlighted
Champion III
Champion III

Another solution in UI chart expr try like

Using straight table add dims as
PersonKey, EventKey
3-Exprs
=Aggr(Rank(-PersonKey,4,1), EventKey, PersonKey) //Label this as Ranking
=Count(TOTAL <EventKey> EventKey) //Label this as Cnt
=Ranking & ' of '& Cnt //This will give you count of individuals

Capture.PNG

View solution in original post

Highlighted
Contributor III
Contributor III

Hi,

Thank you for the help. I was able to get this to work but is there any way to hide and or calculate this in the front end without displaying the ranking and cnt columns? I attempted the backend update but it is duplicating the total number of events across each person in the data set.

Thank you so much!
Highlighted
Champion III
Champion III

Got to presentation tab of your straight table and check Ranking and Cnt fields and Check radio button that says Hide column.