Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Weighted Average with Text Values

I have a form where submissions include two questions with 5 important levels and 5 options in each, and I want to see the weighted average of each option within each question

Question 1: What is most important to you?

Options: Support, Documentation, Quality, Patience, Speed

Most Important | Important | Neutral | Not Important | Least Important

Question 2: What is most important to you?

Options: Communication, Professionalism, Activity, Completion, Punctuality

Importance Most Important | Important | Neutral | Not Important | Least Important


Results table

SubmissionDateQuestion1AQuestion1BQuestion1CQueston1DQueston1EQuestion2AQuestion2BQuestion2CQueston2DQuestion2E
1/1/17SupportDocumentationPatienceSpeedQualityCommunicationActivityCompletionPunctualityProfessionalism
1/4/17QualitySupportDocumentationPatienceSpeedActivityCommunicationPunctualityProfessionalismCompletion
1/9/17SpeedSupportQualityDocumentationPatiencePunctualityCompletionCommunicationProfessionalismActivity
1/15/17SupportQualityPatienceSpeedDocumentationActivityCompletionProfessionalismCommunicationPunctuality

At the end of the day, I am trying to create a table that changes it's order based on the weighted average score.

SupportDocumentationQualityPatienceSpeed
3.83.22.32.11.8

I created an inline load for the two option sets:

Dim1:

LOAD * INLINE [

  Dim1

  Speed

  Documentation

  Patience

  Support

  Quality

];

Dim2:

LOAD * INLINE [

    Dim2

    Communication

    Activity

    Completion

    Punctuality

    Professionalism

];

In the table, the dimension is set as 'Dim1' and there are two expressions.

1. Unknown

2. RowNo()

Best expression I could come up with was something like this which didn't work.

If(Dim1='Speed',sum(if(Question1A='Speed',5,

                    if(Question1B='Speed',4,

                    if(Question1C='Speed',3,

                    if(Question1D='Speed'=2,

                    if(Question1E='Speed',1,0)))))

Thank you for the help,

Phil

1 Solution

Accepted Solutions
MarcoWedel

Hi,

one possible solution might be:

QlikCommunity_Thread_250055_Pic8.JPG

QlikCommunity_Thread_250055_Pic7.JPG

QlikCommunity_Thread_250055_Pic10.JPG

QlikCommunity_Thread_250055_Pic6.JPG

QlikCommunity_Thread_250055_Pic12.JPG

QlikCommunity_Thread_250055_Pic2.JPG

QlikCommunity_Thread_250055_Pic3.JPG

QlikCommunity_Thread_250055_Pic9.JPG

QlikCommunity_Thread_250055_Pic1.JPG

mapImportance:

Mapping

LOAD Chr(70-RecNo()), Dual(Imp,RecNo()) INLINE [

    Imp

    Least Important

    Not Important

    Neutral

    Important

    Most Important

];

tabQuestions:

CrossTable (QuestTemp,Option)

LOAD * FROM [https://community.qlik.com/thread/250055] (html, codepage is 1252, embedded labels, table is @1);

Join

LOAD Distinct

    QuestTemp,

    'Q'&Mid(QuestTemp,Len(QuestTemp)-1,1) as Question,

    Right(QuestTemp,1) as ImportanceID,

    ApplyMap('mapImportance',Right(QuestTemp,1)) as Importance

Resident tabQuestions;

DROP Field QuestTemp;

hope this helps

regards

Marco

View solution in original post

20 Replies
vinieme12
Champion III
Champion III

Use dual ()

And assign numeric values to your text , search for more content dual () in qlikview help

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

Vineeth,

Would I use dual within the Dim inline load or within the expression on the table itself?

Thank you,

Phil

Anonymous
Not applicable
Author

dual() with inline isn't possible, you could try

unqualify*;

YOUR_DUAL_TABLE:

LOAD dual('First',1) as Field autogenerate(1);

LOAD dual('Second',2) as Field autogenerate(1);

LOAD dual('Third',3) as Field autogenerate(1);

LOAD dual('Fourth',4) as Field autogenerate(1);

...

vinieme12
Champion III
Champion III

Like this

Load Dual (Dim1,Weight) as Dim1 INLINE [

  Dim1,Weight

  Speed,5

  Documentation,4

  Patience,3

  Support,2

  Quality,1

];

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

Maybe I'm thinking about it the wrong way, but I don't want to weight the options - [Speed, Documentation, Patience, etc] but rather the question importance level - [Most Important, Neutral, Least Important,etc].

Question1A (Most Important) = Speed = 5 points

Question1A (Most Important) = Patience = 5 points

Question1C (Neutral) = Speed = 3 points

Question1E (Least Important) = Patience = 1 point

Wouldn't weighting the direction you recommended be the opposite of what I'm looking for?

vinieme12
Champion III
Champion III

Can you post some sample data with the expected output you are looking for? it'll be easier to assist.

Preparing examples for Upload - Reduction and Data Scrambling

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

Can you post some sample data with the expected output you are looking for? it'll be easier to assist.

https://community.qlik.com/docs/DOC-1290

https://www.google.com.sg/search?q=preparing+sample+for+upload+qlik&rls=com.microsoft:en-US&ie=UTF-8...

https://www.google.com.sg/search?q=preparing+sample+for+upload+qlik&rls=com.microsoft:en-US&ie=UTF-8...

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

Test file attached

vinieme12
Champion III
Champion III

Dim1:

LOAD * INLINE [

    Dim1,Dim1Code

    Speed,ADVS

  Documentation,DOCQ

  Patience,PATS

  Support,PROD

  Quality,QUAL

];

Dim2:

LOAD * INLINE [

    Dim2,Dim2Code

    Completion,CHCO

    Communication,COMM

    Activity,DOQU

    Professionalism,PROF

    Punctuality,PUNC

];

MapWeights:

Load * inline [

CODE,CODE_Weight

  ADVS,1

  DOCQ,2

  PATS,3

  PROD,4

  QUAL,5

  CHCO,1

    COMM,2

    DOQU,3

    PROF,4

    PUNC,5

];

2016:

LOAD DateSubmitted,

    Dual(Question1A,Applymap('MapWeights',Question1A)) as Question1A,

    Dual(Question1B,Applymap('MapWeights',Question1B)) as Question1B,

  Dual(Question1C,Applymap('MapWeights',Question1C)) as Question1C,

  Dual(Question1D,Applymap('MapWeights',Question1D)) as Question1D,

  Dual(Question1E,Applymap('MapWeights',Question1E)) as Question1E,

  Dual(Question2A,Applymap('MapWeights',Question2A)) as Question2A,

  Dual(Question2B,Applymap('MapWeights',Question2B)) as Question2B,

  Dual(Question2C,Applymap('MapWeights',Question2A)) as Question2C,

  Dual(Question2D,Applymap('MapWeights',Question2A)) as Question2D,   

  Dual(Question2E,Applymap('MapWeights',Question2E)) as Question2E,

     1 as SurveyCount

FROM

SourceData\WeightedAverage.xls

(biff, embedded labels, table is Sheet1$);

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.