Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all
here is the scenario:
I am working on a dashboard for training department and calculate the percentage of the completion rate.
However, i was asked to capture the completion rate on a daily basis, and save in QV, and every time the training file reloaded, the new completion rate will be calculate and captured, so they can see the trend.
I am not sure how to capture the completion rate in the QV and save on a daily basis. Any advise will be greatly appreciated!
Thanks!
so i figured this out
I saved the set analysis in the variable. I put this in the open document trigger.
and in the script I first tested if I have the qvd(where I will be saving my number)file, if I do, I just concat a inline table,
if not, I will create that inline table. and store that table as a qvd file.
this will only save yesterday's number, it will save the new number in the next reload.
I hope this will help someone else.
I have done this in the past by replicating the calculations in the backend and saving them in a qvw. If calculation is very complex, you can also use a macro to do the capture for you. I haven't done the Macro before, but I have seen some that capture the front end data and save them in QVWs QVDs.
UPDATE: i meant QVDs (and not QVWs)
Can you show me a sample?
what is the back end you are referring to? are you saving on the same QV?
Thank you!
I guess it would be easier if you can share a sample or some rows of data to demonstrate.
Also, I wrote QVW above, but I meant QVD
ID | GEID | GLMSEmpID | GLMSLearner | GLMSLearnerEmail | GLMSDirectManagerEmail | GLMSAssignedTrainingLearnerCompletionStatus | GLMSActivityCode | GLMSAssignedTrainingStatusDate | GLMSAssignedTrainingRegistrationDate | GLMSAssignedTrainingStartDate | GLMSAssignedTrainingEndDate | GLMSAssignedTrainingDueDate | GLMSAssignedTrainingLearnerAgingStatus | GLMSAssignedTrainingLearnerEscalationStatus | GLMSActivityID | GLMSActivityName | GLMSActivityStatus | GLMSActivityStartDate | GLMSActivityEndDate | GLMSActivityCreditHours | GLMSActivityDurationHours | GLMSActivityLanguage | GLMSActivityShowingOnTranscript | GLMSAssignmentName | GLMSAssignmentType | GLMSAssignmentMethod | GLMSAudienceID | GLMSAudienceName | GLMSLearnerStatus | GLMSLearnerCode | GLMSLearnerHireDate | GLMSLearnerJobTitle | GLMSLearnerOfficerTitle | GLMSLearnerGOC | GLMSLearnerGOCDescription | GLMSLearnerCountry | GLMSLearnerRegion | GLMSLearnerMgdSeg03 | GLMSLearnerMgdSeg04 | GLMSLearnerMgdSeg05 | GLMSLearnerMgdSeg06 | GLMSLearnerMgdSeg07 | GLMSLearnerMgdSeg08 | GLMSLearnerMgdSeg09 | GLMSLearnerMgdSeg10 | GLMSLearnerMgdSeg11 | CurrentDate_GLMS_AssignedTraining_Employee |
AA03112 | _1010603112 | 2471188 | Adam, Amesh | Adam.Amesh@aol.com | olaf.fischer@yahoo.com | Completed | CGPGOBSBS15 | 12/18/2015 6:47 | 12/18/2015 6:11 | 12/18/2015 6:11 | 12/18/2015 6:47 | 1/16/2016 16:19 | (w) Completed Coming Due 16-30d | 1107239 | Global Training: Be Smart. Be Secure 2015 | Active | 0.5 | 0.5 | Yes | 2015 Be Smart. Be Secure. NE Non-CW - AA | Mandatory | Audience | 16298 | 2015 Be Smart. Be Secure. | Active | Temp | 2/10/2015 0:00 | SPECIALIST | _1782DE3853 | CATS TSA | IN | APAC | Businesses [L3] | Clients Group [L4] | Markets[L5] | Markets [L6] | Mkts [L7] | Global Admin [L8] | Global Management [L9] | EU CATS [L10] | CATS TSA | 2016-02-28 |
So this is the training file.
so one person can be assigned for multiple courses and the completion rate expression is
=if(num(COUNT( {<Tab={'Training'}, GLMSAssignedTrainingLearnerCompletionStatus={'Completed'}, GLMSAssignedTrainingActiveStatus={'Active'}, GLMSAssignmentType={'Mandatory'},GLMSOverDueStatus-={'Past Due 61~90 Days','Past Due 90 Days And Over','Past Due 31~60 Days'}>} GLMSActivityID)/
COUNT( {<Tab={'Training'}, GLMSAssignedTrainingActiveStatus={'Active'}, GLMSAssignmentType={'Mandatory'},GLMSOverDueStatus-=>} TOTAL GLMSActivityID), '##.#%') >1,'N/A',
num(COUNT( {<Tab={'Training'}, GLMSAssignedTrainingLearnerCompletionStatus={'Completed'}, GLMSAssignedTrainingActiveStatus={'Active'}, GLMSAssignmentType={'Mandatory'},GLMSOverDueStatus-={'Past Due 61~90 Days','Past Due 90 Days And Over','Past Due 31~60 Days'}>} GLMSActivityID)/
COUNT( {<Tab={'Training'}, GLMSAssignedTrainingActiveStatus={'Active'}, GLMSAssignmentType={'Mandatory'},GLMSOverDueStatus-=>} TOTAL GLMSActivityID), '##.#%')
)
and then we want to save this value to a daily table (as your approach, a qvd file)
would this be enough information?
thanks Sunny!
This seems okay, but it would require a lot of time to understand the expression and then implement it in the script. I won't have that much time at work right now, but if someone doesn't come up with a better solution, I can definitely take a look at it
so i figured this out
I saved the set analysis in the variable. I put this in the open document trigger.
and in the script I first tested if I have the qvd(where I will be saving my number)file, if I do, I just concat a inline table,
if not, I will create that inline table. and store that table as a qvd file.
this will only save yesterday's number, it will save the new number in the next reload.
I hope this will help someone else.