Count of Records Related

[philip_radovich]

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

[vishsaggi]

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

[philip_radovich]

I am hoping for something like the attached screenshot. 

[vishsaggi]

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;

[vishsaggi · Accepted solution]

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

[philip_radovich]

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!

[vishsaggi]

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