topic Re: Weighted Average with Text Values in QlikView

Weighted Average with Text Values

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Weighted Average with Text Values

vinieme12 — Wed, 15 Feb 2017 18:01:36 GMT

Use dual ()

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

Re: Weighted Average with Text Values

Anonymous — Wed, 15 Feb 2017 18:05:18 GMT

Vineeth,

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

Thank you,

Phil

Re: Weighted Average with Text Values

Anonymous — Wed, 15 Feb 2017 18:08:35 GMT

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

...

Re: Weighted Average with Text Values

vinieme12 — Wed, 15 Feb 2017 18:13:51 GMT

Like this

Load Dual (Dim1,Weight) as Dim1 INLINE [

Dim1,Weight

Speed,5

Documentation,4

Patience,3

Support,2

Quality,1

];

Re: Weighted Average with Text Values

Anonymous — Wed, 15 Feb 2017 20:10:34 GMT

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?

Re: Weighted Average with Text Values

vinieme12 — Thu, 16 Feb 2017 06:21:53 GMT

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

Re: Weighted Average with Text Values

vinieme12 — Thu, 16 Feb 2017 06:34:48 GMT

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

Re: Weighted Average with Text Values

Anonymous — Thu, 16 Feb 2017 06:53:31 GMT

Test file attached

Re: Weighted Average with Text Values

vinieme12 — Thu, 16 Feb 2017 07:11:20 GMT

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

Re: Weighted Average with Text Values

Anonymous — Thu, 16 Feb 2017 07:39:36 GMT

First of all, thank you for your assistance.

As I mentioned before, and I am likely reading it wrong, but doesn't this apply a 1-5 weight to the Dim values?

When I think of the intended outcome, we want to count the number of times each Dim value is submitted in Question1A and give each of those a score of 5. Then down the line for Question1B=4, 1C=3, 1D=2, 1E=1. Each Dim value would get a total score given the weighting of Question1A-E and the frequency.

Where in your example does Question1A represent 5 points?

Re: Weighted Average with Text Values

vinieme12 — Thu, 16 Feb 2017 07:48:25 GMT

can you post a snapshot of what exactly do you want to measure of see?

a table in excel maybe

Re: Weighted Average with Text Values

Anonymous — Thu, 16 Feb 2017 07:56:32 GMT

We plan to run these reports based on a state value through NPrinting for the final report. The QVW I attached includes the objects we're using in NPrinting, but I can't get the ranking to work. The idea is that for each state (cycle dimension in NPrinting) we will be able to see a weighted average of how important each one of those Dim values were. One state may say that on average Speed and Patience are more important than the others.

I just realized I had typos in my original request that don't line up to the QVW I attached. The final table should be ranked 1 through 5 in order of most to least importance, rather than the average decimal score.

Re: Weighted Average with Text Values

vinieme12 — Thu, 16 Feb 2017 08:13:12 GMT

Sorry, but this is not helping me visualize what you need

Re: Weighted Average with Text Values

Anonymous — Mon, 20 Feb 2017 18:05:41 GMT

Vineeth,

We are trying to run NPrinting reports that has two separate tables for the two weighted average displays. With the five options in each question, we want to see how important each option is ranked. When we get a submission, the user is telling us how they rank the five options from Most Important to Least Important.

For the reports, we use the State field to cycle the results of each printout. Say we have 5 responses for Kansas:

SubmissionDate	Question1A	Question1B	Question1C	Queston1D	Queston1E
1/1/17	Support	Documentation	Patience	Speed	Quality
1/4/17	Quality	Support	Documentation	Patience	Speed
1/9/17	Patience	Support	Quality	Documentation	Speed
1/15/17	Support	Quality	Patience	Speed	Documentation
1/19/17	Support	Quality	Documentation	Patience	Speed

Support: Most Important - 3, Important - 2, Neutral - 0, Not Important - 0, Least Important - 0

Support Total:23 / 5 = 4.6 --> Ranked 1

Quality: Most Important - 1, Important - 2, Neutral - 1, Not Important - 0, Least Important - 1

Quality Total:17 / 5 = 3.4 --> Ranked 2

Documentation: Most Important - 0, Important - 1, Neutral - 2, Not Important - 1, Least Important - 1

Documentation Total: 13 / 5 = 2.4 --> Ranked 3

Patience: Most Important - 1, Important - 0, Neutral - 2, Not Important - 2, Least Important - 0

Patience Total:13 / 5 = 2.4 --> Ranked 4

Speed: Most Important - 0, Important - 0, Neutral - 0, Not Important - 2, Least Important - 3

Speed Total:7 / 5 = 2.2 --> Ranked 5

Kansas Report Output:

Support	Quality	Documentation	Patience	Speed
1	2	3	4	5

Let me know if you need anything else clarified. Thank you!

Re: Weighted Average with Text Values

vinieme12 — Tue, 21 Feb 2017 07:01:07 GMT

how did we derive 23 here?

Support Total:23 / 5

Re: Weighted Average with Text Values

Anonymous — Tue, 21 Feb 2017 19:19:53 GMT

Support:

Most Important - 3 responses = 15 points

Important - 2 responses = 8 points

Neutral - 0 responses = 0 points

Not Important - 0 responses = 0 points

Least Important - 0 responses = 0 points

15+8 = 23

Support Total:23 / 5 = 4.6 --> Ranked 1

Most Important answers are worth 5 points, Important are worth 4, and so on.

Thank you!

Re: Weighted Average with Text Values

Anonymous — Mon, 06 Mar 2017 15:34:08 GMT

Any thoughts? I have been really stuck trying to figure it out.

Thank you for the help

Re: Weighted Average with Text Values

Mon, 06 Mar 2017 16:50:21 GMT

Hi,

I have attached a small sample application, have a look at it and hope it helps your scenario!

Re: Weighted Average with Text Values

MarcoWedel — Tue, 07 Mar 2017 00:24:06 GMT

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