Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
alexpanjhc
Specialist
Specialist
‎2016-02-29 09:50 AM

Capture a number

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!

411 Views
0 Likes
1 Solution

Accepted Solutions
alexpanjhc
Specialist
Specialist
‎2016-03-11 04:06 PM
Author

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.

View solution in original post

327 Views
0 Likes
6 Replies
sunny_talwar
MVP
MVP
‎2016-02-29 09:52 AM

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)

327 Views
0 Likes
alexpanjhc
Specialist
Specialist
‎2016-02-29 09:54 AM
Author

In response to sunny_talwar

Can you show me a sample?

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

Thank you!

327 Views
0 Likes
sunny_talwar
MVP
MVP
‎2016-02-29 09:57 AM

In response to alexpanjhc

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

327 Views
0 Likes
alexpanjhc
Specialist
Specialist
‎2016-02-29 10:17 AM
Author

In response to sunny_talwar
IDGEIDGLMSEmpIDGLMSLearnerGLMSLearnerEmailGLMSDirectManagerEmailGLMSAssignedTrainingLearnerCompletionStatusGLMSActivityCodeGLMSAssignedTrainingStatusDateGLMSAssignedTrainingRegistrationDateGLMSAssignedTrainingStartDateGLMSAssignedTrainingEndDateGLMSAssignedTrainingDueDateGLMSAssignedTrainingLearnerAgingStatusGLMSAssignedTrainingLearnerEscalationStatusGLMSActivityIDGLMSActivityNameGLMSActivityStatusGLMSActivityStartDateGLMSActivityEndDateGLMSActivityCreditHoursGLMSActivityDurationHoursGLMSActivityLanguageGLMSActivityShowingOnTranscriptGLMSAssignmentNameGLMSAssignmentTypeGLMSAssignmentMethodGLMSAudienceIDGLMSAudienceNameGLMSLearnerStatusGLMSLearnerCodeGLMSLearnerHireDateGLMSLearnerJobTitleGLMSLearnerOfficerTitleGLMSLearnerGOCGLMSLearnerGOCDescriptionGLMSLearnerCountryGLMSLearnerRegionGLMSLearnerMgdSeg03GLMSLearnerMgdSeg04GLMSLearnerMgdSeg05GLMSLearnerMgdSeg06GLMSLearnerMgdSeg07GLMSLearnerMgdSeg08GLMSLearnerMgdSeg09GLMSLearnerMgdSeg10GLMSLearnerMgdSeg11CurrentDate_GLMS_AssignedTraining_Employee
AA03112_10106031122471188Adam, AmeshAdam.Amesh@aol.comolaf.fischer@yahoo.comCompletedCGPGOBSBS1512/18/2015 6:4712/18/2015 6:1112/18/2015 6:1112/18/2015 6:471/16/2016 16:19(w) Completed Coming Due 16-30d1107239Global Training: Be Smart. Be Secure 2015Active0.50.5Yes2015 Be Smart. Be Secure. NE Non-CW - AAMandatoryAudience162982015 Be Smart. Be Secure. ActiveTemp2/10/2015 0:00SPECIALIST                    _1782DE3853CATS TSAINAPAC Businesses [L3]Clients Group [L4] Markets[L5]Markets [L6] Mkts [L7]Global  Admin [L8]Global Management [L9]EU CATS [L10]CATS TSA2016-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!

327 Views
0 Likes
sunny_talwar
MVP
MVP
‎2016-02-29 10:20 AM

In response to alexpanjhc

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

327 Views
0 Likes
alexpanjhc
Specialist
Specialist
‎2016-03-11 04:06 PM
Author

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.

328 Views
0 Likes