Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the follow situation:
table: CUSTOMER
customer_id | customer_name
table: CAMPAING
campaing_id | campaing_name
table: PARTICIPATION
campaing_id | customer_id
Many customers can participate of many campaing, righ?
I need a chart that returns:
numofcampaings | qttyofusers
1 | 15320 - number os persons that participated of just 1 campaing
2 | 650 - number os persons that participated of 2 campaings
3 | 120 - number os persons that participated of 3 campaings
4 | 25 - number os persons that participated of 4 campaings
Anyone can help me?
thank you
hello,
the way to go is having a aggregated calculated dimension :
aggr(count(campaign_id),customer_id)
and display expression :
count(distinct customer_id)
SELECT customer_id, customer_name, Count(campaign_id) As campaign_count
FROM customer, participation
WHERE customer.customer_id = participation.customer_id
GROUP BY customer_id, customer_name
You could use that in place of loading the Customer table by itself. You would still probably want to load the Campaign and Participation tables as are. Then in your expressions, use Set Analysis to get the counts.
Sum({<campaign_count = {1}>}, 1, 0) would be the number that participated in 1 campaign.
The above query is Oracle SQL, here's T-SQL is you prefer:
SELECT customer_id, customer_name, Count(campaign_id) As campaign_count
FROM customer INNER JOIN participation ON customer.customer_id = participation.customer_id
GROUP BY customer_id, customer_name
I'd also expect that this could be done by simply loading your tables individually and then using expressions to do all the dirty work.
hello,
the way to go is having a aggregated calculated dimension :
aggr(count(campaign_id),customer_id)
and display expression :
count(distinct customer_id)