Solved: Re: Weighted Average with Text Values - Qlik Community

Anonymous · ‎2017-02-15

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

SubmissionDate	Question1A	Question1B	Question1C	Queston1D	Queston1E	Question2A	Question2B	Question2C	Queston2D	Question2E
1/1/17	Support	Documentation	Patience	Speed	Quality	Communication	Activity	Completion	Punctuality	Professionalism
1/4/17	Quality	Support	Documentation	Patience	Speed	Activity	Communication	Punctuality	Professionalism	Completion
1/9/17	Speed	Support	Quality	Documentation	Patience	Punctuality	Completion	Communication	Professionalism	Activity
1/15/17	Support	Quality	Patience	Speed	Documentation	Activity	Completion	Professionalism	Communication	Punctuality

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

Support	Documentation	Quality	Patience	Speed
3.8	3.2	2.3	2.1	1.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

MarcoWedel · ‎2017-03-06

Hi,

one possible solution might be:

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

vinieme12 · ‎2017-02-15

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 · ‎2017-02-15

Vineeth,

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

Thank you,

Phil

Anonymous · ‎2017-02-15

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 · ‎2017-02-15

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 · ‎2017-02-15

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 · ‎2017-02-16

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 · ‎2017-02-16

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

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

Anonymous · ‎2017-02-16

Test file attached

vinieme12 · ‎2017-02-16

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.