Counting Frequency Across different dimensions

[kwhwan]

My data structure looks like this. In each dinner there are many participants (up to 10 in the real case) and are stored into different fields.

Dinner_ID	Money Spent	Per Head Average	Participant 1	Participant 2	Participant 3	Participant 4	Participant 5
1	$500	$100	Peter	Mary	John	Tom	Jerry
2	$350	$87.5	Jerry	Jasmine	Peter	Kingsley
3	$600	$200	Kingston	Peter	Jerry
4	$300	$75	Rick	Jenny	Jerry	Peter
5	$500	$166.7	Bosco	Ken	Tom

Part 1

If I want to count how many dinners the participant has joined in total, and list them into a table/pivot table like below, what should I do?

e.g. the desired result

Participant	Count
Peter	4
Jerry	3
Tom	2
Jerry	2
Rick	1
Mary	1
Kingston	1
Kingsley	1
Ken	1
John	1
Jenny	1
Jasmine	1
Bosco	1

Part 2

If I want to count how many dinners the participant has joined in total, and list them into a table/pivot table like below, what should I do?

e.g. Peter = $462.5

Qlik Sense Desktop 

[hic · Accepted solution]

Split it up into two tables. Then everything becomes much clearer, and you can do a Count(distinct Dinner_ID) per Participant.

Dinners:
Load Dinner_ID, [Money Spent], [Per Head Average] From <Source> ;

Participants:
CrossTable (Participant, Name)
Load Dinner_ID, [Participant 1], [Participant 2], [Participant 3], [Participant 4], [Participant 5] From <Source> ;

See also https://community.qlik.com/t5/Qlik-Design-Blog/The-Crosstable-Load/ba-p/1468083 

[kwhwan]

@hic 

Thanks for the solution and it works!

May I know if I want to add part 2, should I add another crosstable to it? Many thanks!

Participant	Count	Total Spent
Peter	4	$462.5
Jerry	3	$xxxx
Tom	2	$xxxx
Jerry	2	$xxxx
Rick	1	$xxxx
Mary	1	$xxxx
Kingston	1	$xxxx
Kingsley	1	$xxxx
Ken	1
John	1
Jenny	1
Jasmine	1
Bosco	1

[hic]

You can use the following measure to calculate the cost per participant:

     Sum([Per Head Average])

Just put it in a new column.