unable to get a percentage completed

[#1]

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

[sunny_talwar]

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

[#3]

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

[sunny_talwar]

Would it be possible to share a sample app to show the issue and pinpoint the issue you are having?

[#5]

not really , as it has company data in and so the table above is a small extract

[sunny_talwar]

See if this helps with confidentiality

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

[#7]

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