Capture a number

[alexpanjhc]

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!

[sunny_talwar]

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)

[alexpanjhc]

Can you show me a sample?

what is the back end you are referring to? are you saving on the same QV?

Thank you!

[sunny_talwar]

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

[alexpanjhc]

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!

[sunny_talwar]

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

[alexpanjhc · Accepted solution]

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.