Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
iamscribe
New Contributor III

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

Tags (1)
1 Solution

Accepted Solutions

Re: Weighted Average with Text Values

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
Esteemed Contributor II

Re: Weighted Average with Text Values

Use dual ()

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

iamscribe
New Contributor III

Re: Weighted Average with Text Values

Vineeth,

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

Thank you,

Phil

robin_hausdoerfer
Valued Contributor III

Re: Weighted Average with Text Values

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
Esteemed Contributor II

Re: Weighted Average with Text Values

Like this

Load Dual (Dim1,Weight) as Dim1 INLINE [

  Dim1,Weight

  Speed,5

  Documentation,4

  Patience,3

  Support,2

  Quality,1

];

iamscribe
New Contributor III

Re: Weighted Average with Text Values

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
Esteemed Contributor II

Re: Weighted Average with Text Values

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

vinieme12
Esteemed Contributor II

Re: Weighted Average with Text Values

iamscribe
New Contributor III

Re: Weighted Average with Text Values

Test file attached

vinieme12
Esteemed Contributor II

Re: Weighted Average with Text Values

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$);