Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to understand a relationship whereby you have people who have registered for a course and then possibly completed that course and getting the percentage of people who have. The table i'm using is as follows
Person_id | Qual | Stage | Registered | Type | CompletionTime |
211482 | Course1 | Comp End | 201601 | R | NULL |
211482 | Course1 | Comp End | 201601 | C | 15 |
227449 | Course1 | Current | 201601 | R | NULL |
227449 | Course1 | Current | NULL | C | NULL |
123456 | Course1 | Comp End | 201510 | R | |
123456 | Course1 | Comp End | 201601 | C |
Registered denote date of when that stage was done
Type denotes -registered R or completed C
So im using Registered as a dimension
and the expression i'm using is
=
((
count(
{< RevisedShotQuals.Type = {'C'} >}
RevisedShotQuals.Person_id)
)
/
(
count(
{< RevisedShotQuals.Type = {'R'} >}
RevisedShotQuals.Person_id)
))*100
so because 2 people registered in 201601 and 2 people completed in 201601 = 100% completion
however what im trying to get is - of the 2 people that registered in 201601 only 1 of those actually completed so should = 50%
should the dimension be grouped differently or do i tweak the expression?
any help please
May be this:
=((
Count({<RevisedShotQuals.Type = {'C'}, RevisedShotQuals.Person_id = {"=Count({<RevisedShotQuals.Type = {'R'}>} RevisedShotQuals.Person_id) > 0"}>} RevisedShotQuals.Person_id))
/
(Count({<RevisedShotQuals.Type = {'R'}>} RevisedShotQuals.Person_id))
)*100
Thank you
That kind of works and certainly gives the right answer in the sample above
but now it also grouping on the Registered date which im guessing is down to the dimension used?
so the calc suggested now returns the percentage of those who registered and completed in the same month aligning to the same individual, i'm just trying to understand those that registered in a particular month and the percentage of those that registered regardless of month.
e.g. the example below should show 66% , but using the suggested calc would only show 33%
Person_id
Qual | Stage | Registered | Type | CompletionTime | |
211482 | Course1 | Comp End | 201601 | R | NULL |
211482 | Course1 | Comp End | 201601 | C | 15 |
227449 | Course1 | Current | 201601 | R | NULL |
227449 | Course1 | Current | NULL | C | NULL |
123456 | Course1 | Comp End | 201601 | R | |
123456 | Course1 | Comp End | 201605 | C |
The percentage will change over time as more people complete the course
Would it be possible to share a sample app to show the issue and pinpoint the issue you are having?
not really , as it has company data in and so the table above is a small extract
See if this helps with confidentiality
Preparing examples for Upload - Reduction and Data Scrambling
Appreciate the help Sunny, but its a bit too big with too much sensitive info to provide a sample
if ive got time today i'll create a smaller inline app and upload that instead for clarity